package com.borya.dao.impl;

import com.borya.code.ErrcodePrefix;
import com.borya.constant.RedisConstant;
import com.borya.dao.UserDAO;
import com.borya.framework.annotation.Component;
import com.borya.framework.core.ServerSqlSession;
import com.borya.framework.util.ObjectUtils;
import com.borya.model.db.*;
import com.borya.model.entity.*;
import com.borya.model.enumcation.MerchantPowerLimit;
import com.borya.model.enumcation.OtherConfType;
import com.borya.util.AliOSSUtil;
import com.borya.util.Constant;
import com.borya.util.DateUtils;
import com.borya.util.StringUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;

import java.time.Clock;
import java.util.*;
import java.util.stream.Collectors;

//import com.borya.util.DbConnectionUtils;

@Component("userDAOImpl")
public class UserDAOImpl extends BaseDAOImpl implements UserDAO {

	private Logger log = Logger.getLogger(getClass());
	private long CREDITVALITTIME = Constant.CREDITVALITTIME;
	private long model_protect_time = Constant.model_protect_time;

	@Override
	public User getUser(final String userId) {
		StringBuilder sql = new StringBuilder(256);
		sql.append("SELECT a.is_main,a.dealer_id,a.userid,a.citycode,a.cityname,a.invite_code");
		sql.append(",a.user_name,b.left_fee,a.phone,b.deduction_fee,a.flag,a.source_type,a.detail_type");
		sql.append(" FROM tb_user_client a");
		sql.append(" left join tb_user_account b ");
		sql.append(" on a.userid=b.user_id");
		sql.append(" WHERE a.userid=?");
		try (ServerSqlSession session = getSqlDataSource().session();) {
			return session.executeQuery(sql.toString(), rs -> {
				if (rs != null && rs.next()) {
					User user = new User();
					user.setUserId(rs.getString("userid"));
					user.setCityCode(rs.getString("citycode"));
					user.setCityName(rs.getString("cityname"));
					user.setInviteCode(rs.getString("invite_code"));
					user.setLeftFee(rs.getLong("left_fee"));
					user.setUserName(rs.getString("user_name"));
					user.setDealerId(rs.getString("dealer_id"));

					user.setPhone(rs.getString("phone"));
					user.setIsMain(rs.getInt("is_main"));
					user.setDeduction_fee(rs.getLong("deduction_fee"));
					user.setFlag(rs.getInt("flag"));
					user.setSourceType(rs.getInt("source_type"));
					user.setDetailType(rs.getInt("detail_type"));
					return user;
				}
				log.warn("DB not found user,userId=" + userId);
				return null;
			}, userId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public User getSdkUser(String userId, Integer detailType) {
		StringBuilder sql = new StringBuilder(256);
		sql.append("SELECT a.dealer_id,a.userid,a.citycode,a.cityname,a.tmsi,");
		sql.append("a.user_name,a.flag,a.source_type,a.detail_type,a.last_time FROM ");
		sql.append("tb_user_client a JOIN sdk_user_mapper b ON a.userid=b.km_user_id");
		sql.append(" WHERE b.detail_type=? AND b.ext_user_id=? ");
		sql.append("AND a.flag=1 AND a.source_type=3 ORDER BY createtime DESC LIMIT 1");
		try (ServerSqlSession session = getSqlDataSource().session()) {
			return session.executeQuery(sql.toString(), rs -> {
				if (rs != null && rs.next()) {
					User user = new User();
					user.setDealerId(rs.getString("dealer_id"));
					user.setUserId(rs.getString("userid"));
					user.setCityCode(rs.getString("citycode"));
					user.setCityName(rs.getString("cityname"));
					user.setTmsi(rs.getLong("tmsi"));
					user.setInviteCode(rs.getString("invite_code"));
					user.setLeftFee(rs.getLong("left_fee"));
					user.setUserName(rs.getString("user_name"));
					user.setDealerId(rs.getString("dealer_id"));
					user.setPhone(rs.getString("phone"));
					user.setFlag(rs.getInt("flag"));
					user.setSourceType(rs.getInt("source_type"));
					user.setDetailType(rs.getInt("detail_type"));
					user.setLastTime(rs.getLong("last_time"));
					return user;
				}

				return null;
			}, detailType, userId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public boolean addSdkUser(boolean userExists, boolean merchantExits, SelfRegistering registering) {
		List<String> sql_list = new ArrayList<>(7);
		StringBuilder sb = new StringBuilder();
		if (!userExists) {
			sb.append("INSERT INTO sdk_user_mapper SET ");
			sb.append("`km_user_id`='").append(registering.getUser_id()).append("',");
			sb.append("`ext_user_id`='").append(registering.getExtUserId()).append("',");
			sb.append("`detail_type`=").append(registering.getDetailType()).append(",");
			sb.append("`create_time`=").append(registering.getCreateTime());
			sql_list.add(sb.toString());

			sb.delete(0, sb.length());
			sb.append("INSERT INTO tb_user_account SET ");
			sb.append("`user_id`='").append(registering.getUser_id()).append("'");
			sql_list.add(sb.toString());
		}

		sb.delete(0, sb.length());
		if (!merchantExits) {
			sb.append("INSERT INTO tb_merchant SET ");
			sb.append("`merchant_id`='").append(registering.getMerchantId()).append("',");
			sb.append("`user_type_id`=").append(registering.getUser_type_id()).append(",");
			sb.append("`dealer_id`='").append(registering.getDealer_id()).append("',");
			sb.append("`company_name`='").append(registering.getCompany_name()).append("',");
			sb.append("`createtime`=").append(registering.getCreateTime()).append(",");
			sb.append("`merchant_type`=").append(registering.getMerchantType()).append(",");
			sb.append("`status`=").append(registering.getStatus()).append(",");
			sb.append("`modify_time`=").append(registering.getCreateTime()).append(",");
			sb.append("`ytsk_sub_merchantids`=''");
			sb.delete(0, sb.length());

			sb.append("INSERT INTO tb_merchant_account SET ");
			sb.append("`dealer_id`='").append(registering.getDealer_id()).append("',");
			sb.append("`update_date`='").append(DateUtils.formatDateByFormat(registering.getCreateTime(), "yyyyMMdd"))
					.append("',");
			sb.append("`merchant_id`='").append(registering.getMerchantId()).append("'");
		} else {
			sb.append("UPDATE tb_merchant SET ");
			sb.append("`merchant_id`='").append(registering.getMerchantId()).append("',");
			sb.append("`company_name`='").append(registering.getCompany_name()).append("',");
			sb.append("`merchant_type`=").append(registering.getMerchantType()).append(",");
			sb.append("`status`=").append(registering.getStatus()).append(",");
			sb.append("`modify_time`=").append(registering.getCreateTime()).append(",");
			sb.append(" WHERE ").append("`merchant_id`='").append(registering.getMerchantId()).append("'");
		}
		sql_list.add(sb.toString());

		sb.delete(0, sb.length());
		if (!userExists) {
			sb.append("INSERT INTO tb_user_client SET ");
			sb.append("`userid`='").append(registering.getUser_id()).append("',");
			sb.append("`phone`='").append(registering.getPhone()).append("',");
			sb.append("`dealer_id`='").append(registering.getDealer_id()).append("',");
			sb.append("`createtime`=").append(registering.getCreateTime()).append(",");
			sb.append("`user_name`='").append(registering.getUser_name()).append("',");
			sb.append("`last_time`=").append(registering.getCreateTime()).append(",");
			sb.append("`citycode`='").append(registering.getCityCode()).append("',");
			sb.append("`cityname`='").append(registering.getCityName()).append("',");
			sb.append("`user_type_id`=").append(registering.getUser_type_id()).append(",");
			sb.append("`is_main`=").append(registering.getIs_main()).append(",");
			sb.append("`tmsi`=").append(registering.getCreateTime()).append(",");
			sb.append("`osversion`='").append(registering.getOsversion()).append("',");
			sb.append("`appversion`='").append(registering.getAppversion()).append("',");
			sb.append("`ostype`='").append(registering.getOstype()).append("',");
			sb.append("`phonetype`='").append(registering.getPhonetype()).append("',");
			sb.append("`net`='").append(registering.getNet()).append("',");
			sb.append("`applicationID`='").append(registering.getApplicationID()).append("',");
			sb.append("`host_ip`='").append(registering.getHost_ip()).append("',");
			sb.append("`longitude`='").append(registering.getLongitude()).append("',");
			sb.append("`latitude`='").append(registering.getLatitude()).append("',");
			sb.append("`merchant_id`='").append(registering.getMerchantId()).append("'");
			sql_list.add(sb.toString());

			sb.delete(0, sb.length());
			sb.append("INSERT INTO tb_user_client_detail SET ");
			sb.append("`userid`='").append(registering.getUser_id()).append("',");
			sb.append("`user_name`='").append(registering.getUser_name()).append("',");
			sb.append("`citycode`='").append(registering.getCityCode()).append("',");
			sb.append("`cityname`='").append(registering.getCityName()).append("',");
			sb.append("`createtime`=").append(registering.getCreateTime()).append(",");
			sb.append("`user_type_id`=").append(registering.getUser_type_id()).append(",");
			sb.append("`modifytime`=").append(registering.getCreateTime());
			sql_list.add(sb.toString());

			/** 运营平台消息 **/
			sb.delete(0, sb.length());
			sb.append("用户").append(registering.getPhone(), 0, 3).append("******** 于")
					.append(DateUtils.format(registering.getCreateTime(), "yyyy-MM-dd HH:mm:ss")).append("注册成功");
			final String content = sb.toString();
			sb.delete(0, sb.length());
			sb.append("INSERT INTO tb_message_notice SET ");
			sb.append("msg_id='ECSMSG_").append(StringUtil.randomString(7)).append("',");
			sb.append("msg_type=").append(Constant.MsgType.TYPE6).append(",");
			sb.append("create_time=").append(registering.getCreateTime()).append(".");
			sb.append("exp_time=").append(registering.getCreateTime() + 2 * 60 * 60 * 1000).append(",");
			sb.append("content='").append(content).append("'");
			sql_list.add(sb.toString());
			/** 运营平台消息 **/
		} else {
			sb.append("UPDATE tb_user_client SET ");
			sb.append("`phone`='").append(registering.getPhone()).append("',");
			sb.append("`user_name`='").append(registering.getUser_name()).append("',");
			sb.append("`last_time`=").append(registering.getModifyTime()).append(",");
			sb.append("`citycode`='").append(registering.getCityCode()).append("',");
			sb.append("`cityname`='").append(registering.getCityName()).append("',");
			sb.append("`user_type_id`=").append(registering.getUser_type_id()).append(",");
			sb.append("`is_main`=").append(registering.getIs_main()).append(",");
			sb.append("`tmsi`=").append(registering.getCreateTime()).append(",");
			sb.append("`osversion`='").append(registering.getOsversion()).append("',");
			sb.append("`appversion`='").append(registering.getAppversion()).append("',");
			sb.append("`ostype`='").append(registering.getOstype()).append("',");
			sb.append("`phonetype`='").append(registering.getPhonetype()).append("',");
			sb.append("`net`='").append(registering.getNet()).append("',");
			sb.append("`applicationID`='").append(registering.getApplicationID()).append("',");
			sb.append("`host_ip`='").append(registering.getHost_ip()).append("',");
			sb.append("`longitude`='").append(registering.getLongitude()).append("',");
			sb.append("`latitude`='").append(registering.getLatitude()).append("',");
			sb.append("`merchant_id`='").append(registering.getMerchantId()).append("'");
			sb.append(" WHERE ").append("`userid`='").append(registering.getUser_id()).append("'");
			sql_list.add(sb.toString());

			sb.delete(0, sb.length());
			sb.append("UPDATE tb_user_client_detail SET ");
			sb.append("`user_name`='").append(registering.getUser_name()).append("',");
			sb.append("`citycode`='").append(registering.getCityCode()).append("',");
			sb.append("`cityname`='").append(registering.getCityName()).append("',");
			sb.append("`user_type_id`=").append(registering.getUser_type_id()).append(",");
			sb.append("`modifytime`=").append(registering.getCreateTime());
			sb.append(" WHERE ").append("`userid`='").append(registering.getUser_id()).append("'");
			sql_list.add(sb.toString());
		}

		sb.delete(0, sb.length());
		sb.append("INSERT INTO tb_registor_audit SET ");
		sb.append("`order_id`='").append(registering.getOrderId()).append("',");
		sb.append("`request_phone`='").append(registering.getPhone()).append("',");
		sb.append("`user_id`='").append(registering.getUser_id()).append("',");
		sb.append("`company_name`='").append(registering.getCompany_name()).append("',");
		sb.append("`merchant_type`=").append(registering.getMerchantType()).append(",");
		sb.append("`sign_picture`='").append(registering.getImgNo4()).append("',");
		sb.append("`door_picture_left`='").append(registering.getImgNo1()).append("',");
		sb.append("`door_picture_right`='").append(registering.getImgNo2()).append("',");
		sb.append("`hand_picture`='").append(registering.getImgNo3()).append("',");
		sb.append("`business_licence`='").append(registering.getIdCard()).append("',");
		sb.append("`business_licence_picture`='").append(registering.getBusinessLicensePicture()).append("',");
		sb.append("`devMac`=' ',");
		sb.append("`sign_merge_picture`='").append(registering.getSignMergePicture()).append("',");
		sb.append("`popchannel`='").append(registering.getPopchannel()).append("',");
		sb.append("`super_dealer_id`='").append(registering.getSuper_dealer_id()).append("',");
		sb.append("`popchannel_name`='").append(registering.getPopchannel_name()).append("',");
		sb.append("`super_dealer_name`='").append(registering.getSuper_dealer_name()).append("',");
		sb.append("`merchant_attribute`=").append(registering.getMerchant_attribute()).append("',");
		sb.append("`discount_model`='").append(registering.getDiscountModel()).append("',");
		sb.append("`phone_model`='").append(registering.getPhoneModel()).append("',");
		sb.append("`transfer_model`='").append(registering.getTransterModel()).append("',");
		sb.append("`pack_model`='").append(registering.getPackModel()).append("',");
		sb.append("`dwk_model`='").append(registering.getDwkModel()).append("',");
		sb.append("`user_name`='").append(registering.getUser_name()).append("',");
		sb.append("`city_code`='").append(registering.getCityCode()).append("',");
		sb.append("`city_name`='").append(registering.getCityName()).append("',");
		sb.append("`before_status`=").append(registering.getStatus()).append(",");
		sb.append("`create_time`=").append(registering.getModifyTime()).append(",");
		sb.append("`order_status`=5,");
		sb.append("`dealer_id`='").append(registering.getDealer_id()).append("',");
		sb.append("`period`='").append(registering.getPeriod()).append("',");
		sb.append("`head_picture`='").append(registering.getHeadPicture()).append("',");
		sb.append("`store_address`='").append(registering.getStoreAddress()).append("',");
		sb.append("`scope_id`='").append(registering.getScopeId()).append("',");
		sb.append("`is_manage`=").append(registering.getIsManage()).append(",");
		sb.append("`source_type`=").append(registering.getSourceType()).append(",");
		sb.append("`white_card_pay`=").append(registering.getWhiteCardPay()).append(",");
		sb.append("`white_card_return`=").append(registering.getWhiteCardReturn()).append(",");
		sb.append("`detail_type`=").append(registering.getDetailType()).append(",");
		sb.append("`shop_address`='").append(registering.getShopAddress()).append("',");
		sb.append("`shop_specific_address`='").append(registering.getShopSpecificAddress()).append("'");
		if (2 == registering.getMerchantType()) {
			sb.append("`address`='").append(registering.getAddress()).append("'");
		} else if (1 == registering.getMerchantType()) {
			if (StringUtils.isNotBlank(registering.getIdentityCard())) {
				sb.append(",`identityCard`='").append(registering.getIdentityCard()).append("'");
			}
			if (StringUtils.isNotBlank(registering.getIdentityCardName())) {
				sb.append(",`identityCardName`='").append(registering.getIdentityCardName()).append("'");
			}
			if (StringUtils.isNotBlank(registering.getIdentityCardAddress())) {
				sb.append(",`identityCardAddress`='").append(registering.getIdentityCardAddress()).append("'");
			}
		}
		sql_list.add(sb.toString());

		sb.delete(0, sb.length());
		sb.append("INSERT INTO tb_registor_audit_log SET ");
		sb.append("order_id='").append(registering.getOrderId()).append("',");
		sb.append("create_time=").append(registering.getModifyTime()).append(",");
		sb.append("status=1");
		sql_list.add(sb.toString());

		int[] result = executeBatch(sql_list);
		log.debug("addSdkUser sql result:" + Arrays.toString(result));
		return result != null && result.length > 0;
	}

	@Override
	public User getUserByIdCard(String idCard, int detailType) {
		StringBuilder sql = new StringBuilder(256);
		sql.append("SELECT b.userid");
		sql.append(" FROM tb_user_client_detail b");
		sql.append(" left join tb_user_client a ");
		sql.append(" on a.userid=b.userid");
		sql.append(" WHERE b.paperscode=? and a.detail_type=? and a.flag=1");
		try (ServerSqlSession session = getSqlDataSource().session();) {
			return session.executeQuery(sql.toString(), rs -> {
				if (rs != null && rs.next()) {
					User user = new User();
					user.setUserId(rs.getString("userid"));
					return user;
				}
				return null;
			}, idCard, detailType);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

    @Override
    public User getUserByIdCardIsMain(String idCard, int detailType, Integer isMain) {
        StringBuilder sql = new StringBuilder(256);
        sql.append("SELECT b.userid");
        sql.append(" FROM tb_user_client_detail b");
        sql.append(" left join tb_user_client a ");
        sql.append(" on a.userid=b.userid");
        sql.append(" WHERE b.identity_card=? and a.detail_type=? and a.flag=1 and a.is_main = ? ");
        try (ServerSqlSession session = getSqlDataSource().session()) {
            return session.executeQuery(sql.toString(), rs -> {
                if (rs != null && rs.next()) {
                    User user = new User();
                    user.setUserId(rs.getString("userid"));
                    return user;
                }
                return null;
            }, idCard, detailType, isMain);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return null;
        }
    }

	@Override
	public JobNumberAudit getJobNumAuditByIdCard(String idCard) {
		StringBuilder sql = new StringBuilder(256);
		sql.append("SELECT order_id");
		sql.append(" FROM tb_job_number_audit ");
		sql.append(" WHERE identityCard=? and order_status IN (3,4) ");
		try (ServerSqlSession session = getSqlDataSource().session()) {
			return session.executeQuery(sql.toString(), rs -> {
				if (rs != null && rs.next()) {
					JobNumberAudit audit = new JobNumberAudit();
					audit.setOrderId(rs.getString("order_id"));
					return audit;
				}
				return null;
			}, idCard);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public JobNumberAudit getJobNumAuditByOrderId(String orderId) {
		StringBuilder sql = new StringBuilder(256);
		sql.append("SELECT *");
		sql.append(" FROM tb_job_number_audit ");
		sql.append(" WHERE order_id=? ");
		try (ServerSqlSession session = getSqlDataSource().session()) {
			return session.executeQuery(sql.toString(), rs -> {
				if (rs != null && rs.next()) {
					JobNumberAudit audit = new JobNumberAudit();
					audit.setOrderId(rs.getString("order_id"));
					audit.setSonUserName(rs.getString("son_user_name"));
					audit.setSonUserPhone(rs.getString("son_user_phone"));
					audit.setCityName(rs.getString("city_name"));
					audit.setCityCode(rs.getString("city_code"));
					audit.setIdentityCard(rs.getString("identityCard"));
					audit.setIdentityCardAddress(rs.getString("identityCardAddress"));
					audit.setPeriod(rs.getString("period"));
					audit.setUserId(rs.getString("user_id"));
                    audit.setDealerId(rs.getString("dealer_id"));
                    audit.setMerchantId(rs.getString("merchant_id"));
                    audit.setMainUserId(rs.getString("main_user_id"));
                    audit.setIdentifyPhotoHead(rs.getString("identify_photo_head"));
                    audit.setIdentifyPhotoReverse(rs.getString("identify_photo_reverse"));
                    audit.setIdentifyPhotoFront(rs.getString("identify_photo_front"));
                    audit.setIdentifyPhotoHand(rs.getString("identify_photo_hand"));
                    audit.setRelationType(rs.getInt("relation_type"));
                    audit.setDetailType(rs.getInt("detail_type"));
                    audit.setAuditTime(rs.getString("audit_time"));
                    return audit;
				}
				return null;
			}, orderId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public User getUserByPhone(final String phone) {
		StringBuilder sql = new StringBuilder(256);
		sql.append("SELECT a.dealer_id,a.userid,a.citycode,a.cityname,a.invite_code");
		sql.append(",a.user_name,b.left_fee,a.phone,a.flag,a.source_type,a.detail_type");
		sql.append(" FROM tb_user_client a");
		sql.append(" left join tb_user_account b ");
		sql.append(" on a.userid=b.user_id");
		sql.append(" WHERE a.phone=? ");
		sql.append(" and a.flag=1 and a.source_type=1 ");
		sql.append(" order by createtime desc limit 1");
		try (ServerSqlSession session = getSqlDataSource().session();) {
			return session.executeQuery(sql.toString(), rs -> {
				if (rs != null && rs.next()) {
					User user = new User();
					user.setDealerId(rs.getString("dealer_id"));
					user.setUserId(rs.getString("userid"));
					user.setCityCode(rs.getString("citycode"));
					user.setCityName(rs.getString("cityname"));
					user.setInviteCode(rs.getString("invite_code"));
					user.setLeftFee(rs.getLong("left_fee"));
					user.setUserName(rs.getString("user_name"));
					user.setDealerId(rs.getString("dealer_id"));
					user.setPhone(rs.getString("phone"));
					user.setFlag(rs.getInt("flag"));
					user.setSourceType(rs.getInt("source_type"));
					user.setDetailType(rs.getInt("detail_type"));
					return user;
				}

				return null;
			}, phone);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public User getUserByPhone2(String phone) {
        try (ServerSqlSession session = getSqlDataSource().session()) {
			StringBuilder sb = new StringBuilder();
			sb.append("SELECT dealer_id,userid,citycode,cityname,invite_code");
			sb.append(",user_name,phone,flag,source_type");
			sb.append(" FROM tb_user_client");
			sb.append(" WHERE phone=? ");
            sb.append(" and flag=1 and source_type=1 ");
			sb.append(" limit 1");

			return session.executeQuery(sb.toString(), rs -> {
				if (rs != null && rs.next()) {
					User user = new User();
					user.setDealerId(rs.getString("dealer_id"));
					user.setUserId(rs.getString("userid"));
					user.setCityCode(rs.getString("citycode"));
					user.setCityName(rs.getString("cityname"));
					user.setInviteCode(rs.getString("invite_code"));
					user.setUserName(rs.getString("user_name"));
					user.setDealerId(rs.getString("dealer_id"));
					user.setPhone(rs.getString("phone"));
					user.setFlag(rs.getInt("flag"));
					user.setSourceType(rs.getInt("source_type"));
					return user;
				}

				return new User();
			}, phone);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public User getUserByInviteCode(final String dealerId) {
		if (ObjectUtils.isNullOrEmpty(dealerId)) {
			log.warn("deviceid superDealerid is empty");
			return null;
		}
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sql = new StringBuilder(256);
			sql.append("SELECT dealer_id,userid,citycode,cityname,invite_code,user_name,phone");
			sql.append(" FROM tb_user_client");
			sql.append(" WHERE dealer_id=?");
			sql.append(" and is_main=1");
			sql.append(" limit 1");
			return session.executeQuery(sql.toString(), rs -> {
				if (rs != null && rs.next()) {
					User user = new User();
					user.setDealerId(rs.getString("dealer_id"));
					user.setUserId(rs.getString("userid"));
					user.setCityCode(rs.getString("citycode"));
					user.setCityName(rs.getString("cityname"));
					user.setInviteCode(rs.getString("invite_code"));
					user.setUserName(rs.getString("user_name"));
					user.setPhone(rs.getString("phone"));
					return user;
				}
				log.warn("DB not found user,dealerId=" + dealerId);
				return null;
			}, dealerId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public boolean updateUser(User user) {
		StringBuilder sql = new StringBuilder(256);
		;
		Object[] params;
		if ("0".equals(user.getLatitude()) || "0".equals(user.getLongitude())) {
			sql.append("UPDATE tb_user_client SET ");
			sql.append(" tmsi=?,osversion=?,appversion=?,ostype=?");
			sql.append(",phonetype=?,net=?,applicationID=?,last_time=?,host_ip=?");
			sql.append(" WHERE userid=?");
			params = new Object[] { user.getTmsi(), user.getOsVersion(), user.getAppVersion(), user.getOsType(),
					user.getPhoneType(), user.getNet(), user.getApplicationID(), user.getLastTime(), user.getHostIp(),
					user.getUserId() };
		} else {
			sql.append("UPDATE tb_user_client SET ");
			sql.append(" tmsi=?,osversion=?,appversion=?,ostype=?");
			sql.append(",phonetype=?,net=?,applicationID=?,last_time=?,host_ip=?,longitude=?,latitude=?");
			sql.append(" WHERE userid=?");
			params = new Object[] { user.getTmsi(), user.getOsVersion(), user.getAppVersion(), user.getOsType(),
					user.getPhoneType(), user.getNet(), user.getApplicationID(), user.getLastTime(), user.getHostIp(),
					user.getLongitude(), user.getLatitude(), user.getUserId() };
		}

		return executeUpdate(sql.toString(), params);
	}

	@Override
	public boolean updateTmsi(String userId, String curTime) {
		String sql = "UPDATE tb_user_client SET tmsi=? WHERE userid=?";
		Object[] params = { curTime, userId };
		return executeUpdate(sql, params);
	}

	@Override
	public boolean updateUserCity(String cityCode, String cityName, String userId) {
		String sql = "UPDATE tb_user_client SET citycode=? ,cityname=? WHERE userid=?";
		Object[] params = { cityCode, cityName, userId };
		return executeUpdate(sql, params);
	}

	@Override
	public Merchant getMerchant(final String dealerId) {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sb = new StringBuilder(256);
			sb.append("select dealer_id,company_name,user_type_id");
			sb.append(",business_licence,address,popchannel,head_popchannel");
			sb.append(",createtime,devMac,merchant_type,isSignAgreement");
			sb.append(",sign_time,sign_picture,bond,status");
			sb.append(",merchant_attribute,attribute_status,flag,banner_ids");
			sb.append(",super_dealer_id,super_dealer_name,super_invite_code");
			sb.append(",ytsk_status,detail_type");
			sb.append(" from tb_merchant");
			sb.append(" where dealer_id=?");
			sb.append(" limit 1");

			return session.executeQuery(sb.toString(), rs -> {
				if (rs != null && rs.next()) {
					Merchant merchant = new Merchant();
					merchant.setDealerId(dealerId);
					merchant.setCompanyName(rs.getString("company_name"));
					merchant.setUserTypeId(rs.getInt("user_type_id"));
					merchant.setBusinessLicence(rs.getString("business_licence"));
					merchant.setAddress(rs.getString("address"));
					merchant.setPopChannel(rs.getString("popchannel"));
					merchant.setHeadPopChannel(rs.getString("head_popchannel"));
					merchant.setCreateTime(rs.getLong("createtime"));
					merchant.setDevMac(rs.getString("devMac"));
					merchant.setMerchantType(rs.getInt("merchant_type"));
					merchant.setIsSignAgreement(rs.getInt("isSignAgreement"));
					merchant.setSignTime(rs.getLong("sign_time"));
					merchant.setSignPicture(rs.getString("sign_picture"));
					merchant.setBond(rs.getInt("bond"));
					merchant.setStatus(rs.getInt("status"));
					merchant.setMerchant_attribute(rs.getInt("merchant_attribute"));
					merchant.setAttribute_status(rs.getInt("attribute_status"));
					merchant.setFlag(rs.getInt("flag"));
					// merchant.setCreditNum(rs.getLong("credit_num"));
					// merchant.setUserTypeName(rs.getString("userTypeName"));
					merchant.setBannerIds(rs.getString("banner_ids"));
					merchant.setSuperDealerId(rs.getString("super_dealer_id"));
					merchant.setSuperDealerName(rs.getString("super_dealer_name"));
					merchant.setYtskStatus(rs.getInt("ytsk_status"));
					merchant.setDetailType(rs.getInt("detail_type"));
					merchant.setSuperInviteCode(rs.getString("super_invite_code"));
					return merchant;
				}
				return null;
			}, dealerId);
		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
			return null;
		}
	}

	@Override
	public Object[] getMerchantInfo(String dealerId) {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sb = new StringBuilder(256);
			sb.append("select dealer_id,company_name,user_type_id,store_address");
			sb.append(",business_licence,address,popchannel,head_popchannel");
			sb.append(",createtime,devMac,merchant_type,isSignAgreement");
			sb.append(",sign_time,sign_picture,bond,status,merchant_attribute,attribute_status");
			sb.append(",(select nickname from tb_user_type WHERE id =user_type_id limit 1)userTypeName");
			sb.append(",ym_depid,ym_staffno,regist_time,fill_status");
			sb.append(" from tb_merchant");
			sb.append(" where dealer_id=?");
			sb.append(" limit 1");

			Merchant merchant = session.executeQuery(sb.toString(), rs -> {
				if (rs != null && rs.next()) {
					Merchant merchantTmp = new Merchant();
					merchantTmp.setDealerId(dealerId);
					merchantTmp.setCompanyName(rs.getString("company_name"));
					merchantTmp.setUserTypeId(rs.getInt("user_type_id"));
					merchantTmp.setBusinessLicence(rs.getString("business_licence"));
					merchantTmp.setAddress(rs.getString("address"));
					merchantTmp.setPopChannel(rs.getString("popchannel"));
					merchantTmp.setHeadPopChannel(rs.getString("head_popchannel"));
					merchantTmp.setCreateTime(rs.getLong("createtime"));
					merchantTmp.setDevMac(rs.getString("devMac"));
					merchantTmp.setMerchantType(rs.getInt("merchant_type"));
					merchantTmp.setIsSignAgreement(rs.getInt("isSignAgreement"));
					merchantTmp.setSignTime(rs.getLong("sign_time"));
					merchantTmp.setSignPicture(rs.getString("sign_picture"));
					// merchantTmp.setCreditNum(rs.getLong("credit_num"));
					merchantTmp.setUserTypeName(rs.getString("userTypeName"));
					merchantTmp.setBond(rs.getInt("bond"));
					merchantTmp.setStatus(rs.getInt("status"));
					merchantTmp.setMerchant_attribute(rs.getInt("merchant_attribute"));
					merchantTmp.setAttribute_status(rs.getInt("attribute_status"));
					merchantTmp.setStoreAddress(rs.getString("store_address"));
					merchantTmp.setYmDepid(rs.getString("ym_depid"));
					merchantTmp.setYmStaffno(rs.getString("ym_staffno"));
					merchantTmp.setRegistTime(rs.getLong("regist_time"));
                    merchantTmp.setFillStatus(rs.getInt("fill_status"));
					return merchantTmp;
				}
				log.warn("DB not found merchant(tb_merchant),dealer_id=" + dealerId);
				return null;
			}, dealerId);

			if (merchant == null) {
				log.error("DB not found merchant(tb_merchant),dealer_id=" + dealerId);
				return null;
			}

			sb.delete(0, sb.length());
			sb.append("select count(1) from tb_user_client where flag=" + Flag.NORMAL + " and dealer_id=?;");
			Integer num = session.executeQuery(sb.toString(), rs -> {
				return (rs != null && rs.next()) ? rs.getInt(1) : 0;
			}, dealerId);

			if (num == null) {
				log.error("DB connection exception.");
				num = 0;
			}

			sb.delete(0, sb.length());
			sb.append("select count(1) from tb_merchant where popchannel=?");
			int lowerMerchantCount = session.executeQuery(sb.toString(), rs -> {
				return (rs != null && rs.next()) ? rs.getInt(1) : 0;
			}, dealerId);
			// sb.append("select
			// id,level,min_score,max_score,level_name,level_desc");
			// sb.append(" from tf_merchant_credit");
			// sb.append(" order by credit_num asc");
			// Map<Integer, MerchantCredit> map_credit =
			// session.executeQuery(sb.toString(), rs -> {
			// Map<Integer, MerchantCredit> map = new HashMap<Integer,
			// MerchantCredit>();
			// if (rs != null) {
			// while (rs.next()) {
			// MerchantCredit mc = new MerchantCredit();
			// mc.setId(rs.getInt("id"));
			// mc.setLevel(rs.getInt("level"));
			// mc.setMinScore(rs.getLong("min_score"));
			// mc.setMaxScore(rs.getLong("max_score"));
			// mc.setLevelName(rs.getString("level_name"));
			// mc.setLevelDesc(rs.getString("level_desc"));
			// map.put(mc.getLevel(), mc);
			// }
			// }
			// return map;
			// });
			//
			// if (map_credit == null) {
			// log.error("DB tables(tf_merchant_credit) exception,tables is
			// empty.");
			// return null;
			// }

			// return new Object[] { merchant, num, map_credit };
			return new Object[] { merchant, num, lowerMerchantCount };
		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
			return null;
		}
	}

	@Override
	public Boolean updateSignAgeeement(String userId, String signPic, String signMergePicture) {
		StringBuilder sql = new StringBuilder(256);
		sql.append("UPDATE tb_merchant SET ");
		sql.append(" isSignAgreement=1,sign_time=?");
		sql.append(",sign_picture=?,sign_merge_picture=?");
		sql.append(" WHERE dealer_id=?");
		Object[] params = { System.currentTimeMillis(), signPic, signMergePicture, userId };
		return executeUpdate(sql.toString(), params);
	}

	@Override
	public List<UserDataModel> getUserList(String merchantId) {
		try (ServerSqlSession session = getSqlDataSource().session()) {
			StringBuilder sb = new StringBuilder(256);
            sb.append("select u.create_time,a.userid,a.user_name,a.cityname,a.createtime,a.is_main,a.phone,b.left_fee,b.deduction_fee");
            sb.append(" from tb_user_client a ");
            sb.append(" left join tb_user_account b on a.userid=b.user_id ");
            sb.append(" left join tb_job_number_audit u on u.user_id=a.userid ");
			sb.append(" where a.merchant_id=? and a.flag != -1 ");
			sb.append(" order by a.createtime asc ");
			return session.executeQuery(sb.toString(), rs -> {
				List<UserDataModel> list = new ArrayList<>();
				if (rs != null) {
					while (rs.next()) {
						UserDataModel u = new UserDataModel();
                        u.setUserId(rs.getString("a.userid"));
                        u.setUserName(rs.getString("a.user_name"));
                        u.setCityName(rs.getString("a.cityname"));
                        if (StringUtils.isBlank(rs.getString("u.create_time"))) {
                            u.setCreateTime(rs.getString("a.createtime"));
                        } else {
                            u.setCreateTime(rs.getString("u.create_time"));
                        }
                        u.setIsMain(rs.getString("a.is_main"));
                        u.setPhone(rs.getString("a.phone"));
						u.setDeductionFee(rs.getString("b.deduction_fee"));
						u.setLeftFee(rs.getString("b.left_fee"));
						list.add(u);
					}
				}
				return list;
			}, merchantId);
		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
			return null;
		}
	}

    @Override
    public List<UserDataModel> getUserDetailListByIdCard(String idCard, Integer detailType) {
        try (ServerSqlSession session = getSqlDataSource().session()) {
            StringBuilder sb = new StringBuilder(256);
            sb.append("select d.userid,d.user_name,d.cityname,d.createtime ");
            sb.append(" from tb_user_client c LEFT JOIN tb_user_client_detail d on d.userid = c.userid ");
            sb.append(" where d.identity_card =? and c.flag !=-1 and c.is_main = 1 and c.detail_type = ? ");
            sb.append(" order by d.createtime asc ");
            return session.executeQuery(sb.toString(), rs -> {
                List<UserDataModel> list = new ArrayList<>();
                if (rs != null) {
                    while (rs.next()) {
                        UserDataModel u = new UserDataModel();
                        u.setUserId(rs.getString("d.userid"));
                        u.setUserName(rs.getString("d.user_name"));
                        u.setCityName(rs.getString("d.cityname"));
                        u.setCreateTime(rs.getString("d.createtime"));
                        list.add(u);
                    }
                }
                return list;
            }, idCard, detailType);
        } catch (Exception ex) {
            log.error(ex.getMessage(), ex);
            return null;
        }
    }


	@Override
	public boolean bindPhone(String userId, String newPhone) {
		StringBuilder sb = new StringBuilder(256);
		sb.append("update tb_user_client set flag=-1 where phone=? and flag=1 and source_type=1");
		executeUpdate(sb.toString(), newPhone);

		sb.delete(0, sb.length());
		sb.append("update tb_user_client set phone=? where userid=? and source_type=1");
		return executeUpdate(sb.toString(), newPhone, userId);
	}

	@Override
	public MerchantLevelInfo getMerchantLevel(String dealerId) {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			MerchantLevelInfo levelInfo = new MerchantLevelInfo();
			StringBuilder sb = new StringBuilder(256);
			sb.append("select extra_frequency,extra_credit,base_success_frequency");
			sb.append(",base_total_frequency,base_total_score,expect_level ");
			sb.append("from tb_merchant where dealer_id=? limit 1");
			Boolean r1 = session.executeQuery(sb.toString(), rs -> {
				if (rs != null && rs.next()) {
					levelInfo.setMerchantExtraFrequency(rs.getInt(1));
					levelInfo.setMerchantExtraCredit(rs.getInt(2));
					levelInfo.setBase_success_frequency(rs.getInt(3));
					levelInfo.setBase_total_frequency(rs.getInt(4));
					levelInfo.setBase_total_score(rs.getInt(5));
					levelInfo.setForwardLevel(rs.getInt(6));
					return true;
				}
				return null;
			}, dealerId);
			if (r1 == null) {
				log.error("getMerchantLevelInfo r1 return null,sql:" + sb.toString());
				return null;
			}

			long nowTime = Clock.systemDefaultZone().millis();
			sb.delete(0, sb.length());
			sb.append("select count(1) as n,SUM(score) as score,1 as type ");
			sb.append(" from tb_open_map_audit where order_status=2 and audit_status = 1");
			sb.append(" and dealer_id=? and modify_time>=?");
			sb.append(" union all ");
			sb.append("select count(1) as n,SUM(score) as score,2 as type ");
			sb.append(" from tb_open_map_audit where audit_status=2");
			sb.append(" and dealer_id=? and modify_time>=?");
			Boolean r2 = session.executeQuery(sb.toString(), rs -> {
				if (rs != null) {
					int successNums = 0;
					int validNums = 0;
					int getPoints = 0;
					int losePoints = 0;
					int loseNums = 0;
					while (rs.next()) {
						if (rs.getInt("type") == 1) {
							successNums = rs.getInt("n");
							getPoints = rs.getInt("score");
						} else {
							losePoints = rs.getInt("score");
							loseNums = rs.getInt("n");
						}
					}
					validNums = successNums + loseNums;
					levelInfo.setSuccessNums(successNums);
					levelInfo.setValidNums(validNums);
					levelInfo.setGetPoints(getPoints);
					levelInfo.setLosePoints(losePoints);
					return true;
				}
				log.warn("db error");
				return null;
			}, dealerId, nowTime - CREDITVALITTIME, dealerId, nowTime - CREDITVALITTIME);
			if (r2 == null) {
				log.error("getMerchantLevelInfo r2 return null,sql:" + sb.toString());
				return null;
			}

			return levelInfo;
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public MerchantLevelConfig getMerchantLevelConfig() {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sb = new StringBuilder();
			sb.append("select * from tf_bond_config limit 1");
			return session.executeQuery(sb.toString(), rs -> {
				if (rs != null && rs.next()) {
					MerchantLevelConfig m = new MerchantLevelConfig();
					m.setONE_LEVEL(rs.getInt("one_level"));
					m.setONE_NAME(rs.getString("one_name"));
					m.setONE_POWER_DESC(rs.getString("one_desc"));
					m.setTWO_LEVEL(rs.getInt("two_level"));
					m.setTWO_NAME(rs.getString("two_name"));
					m.setTWO_POWER_DESC(rs.getString("two_desc"));
					m.setTHREE_LEVEL(rs.getInt("thr_level"));
					m.setTHREE_NAME(rs.getString("thr_name"));
					m.setTHREE_POWER_DESC(rs.getString("thr_desc"));
					m.setONE_TO_TWO_BOND(rs.getInt("two_bond") - rs.getInt("one_bond"));
					m.setONE_TO_THREE_BOND(rs.getInt("thr_bond") - rs.getInt("one_bond"));
					m.setTWO_TO_THREE_BOND(rs.getInt("thr_bond") - rs.getInt("two_bond"));
					return m;
				}
				return null;
			});
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public Boolean checkBlackUser(String userid) {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sb = new StringBuilder(128);
			sb.append("select 1 from tb_black_user where user_id=? and flag=0 limit 1");
			return session.executeQuery(sb.toString(), rs -> {
				return (rs != null && rs.next());
			}, userid);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public Boolean removeUser(User removeUser, User mainUser, String order_id) {
		List<String> sql_list = new ArrayList<>();
		Long now_time = Clock.systemDefaultZone().millis();
		StringBuilder sb = new StringBuilder();
		sb.append("update tb_user_client set flag=" + removeUser.getFlag());
		sb.append(" where userid='").append(removeUser.getUserId()).append("'");

		sql_list.add(sb.toString());
		sb.delete(0, sb.length());
		sb.append("insert into tb_user_removed set ").append("`removed_userid`='").append(mainUser.getUserId())
				.append("',`user_id`='").append(removeUser.getUserId()).append("',`dealer_id`='")
				.append(removeUser.getDealerId()).append("',`createtime`=").append(now_time).append(",`citycode`='")
				.append(removeUser.getCityCode()).append("',`cityname`='").append(removeUser.getCityName())
				.append("',`sys_order_id`='").append(order_id).append("',`user_name`='")
				.append(removeUser.getUserName()).append("',`phone`='").append(removeUser.getPhone())
				.append("',`left_fee`=").append(removeUser.getLeftFee()).append(",`deduction_fee`=")
				.append(removeUser.getDeduction_fee());

		sql_list.add(sb.toString());
		if (removeUser.getLeftFee() != 0) {
			sb.delete(0, sb.length());
			sb.append("update tb_user_account set left_fee=left_fee+").append(removeUser.getLeftFee())
					.append(" where user_id='").append(mainUser.getUserId()).append("'");
			sql_list.add(sb.toString());
			/** clear userAccount **/
			sb.delete(0, sb.length());
			sb.append("update tb_user_account set ");
			sb.append("left_fee=0,deduction_fee=0");
			sb.append(" where user_id='").append(removeUser.getUserId()).append("'");
			sql_list.add(sb.toString());
			sb.delete(0, sb.length());
			sb.append("insert into tb_user_account_detail set ").append("`dealer_id`='").append(mainUser.getDealerId())
					.append("',`userid`='").append(mainUser.getUserId()).append("',`create_time`=").append(now_time)
					.append(",`type`=9").append(",`fee`=").append(removeUser.getLeftFee()).append(",`fee_remain`=")
					.append(mainUser.getLeftFee() + removeUser.getLeftFee()).append(",`desc`='删除工号入账'")
					.append(",`sys_order_id`='").append(order_id).append("',`to_account_type`=1")
					.append(",`to_account_id`='").append(mainUser.getUserId()).append("',`order_status`=2")
					.append(",`err_code`='").append(ErrcodePrefix.SERVER + "200").append("',`err_msg`='SUCCESS")
					.append("',`terminal_type`=").append(mainUser.getOsType());
			sql_list.add(sb.toString());
		}

		int[] result = executeBatch(sql_list);
		log.debug("remove sql result:" + Arrays.toString(result));
		return result != null && result.length > 0;
	}

	@Override
	public Long getLeftFee(String userId) {
		StringBuilder sql = new StringBuilder(256);
		sql.append("SELECT left_fee FROM tb_user_account ");
		sql.append(" WHERE user_id=?");
		try (ServerSqlSession session = getSqlDataSource().session();) {
			return session.executeQuery(sql.toString(), rs -> {
				if (rs != null && rs.next()) {
					return rs.getLong("left_fee");
				}
				log.error("DB not found user,userId=" + userId);
				return null;
			}, userId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public boolean addUser(JobNumberAudit audit, String invite, Integer auditType, String customer,String livingStatus) {
		List<String> sql_list = new ArrayList<>();
		Long now_time = Clock.systemDefaultZone().millis();
		StringBuilder sb = new StringBuilder();
		// 1成功，2失败
		if (1 == audit.getOrderStatus()) {
			sb.append("insert into tb_user_account set `user_id`='" + audit.getUserId() + "'");
			sql_list.add(sb.toString());

			sb.delete(0, sb.length());
			sb.append("insert into tb_user_client set ");
			sb.append("`userid`='" + audit.getUserId());
			sb.append("',`phone`='" + audit.getSonUserPhone());
			sb.append("',`dealer_id`='" + audit.getDealerId());
			sb.append("',`createtime`=" + now_time);
			sb.append(",`citycode`='" + audit.getCityCode());
			sb.append("',`cityname`='" + audit.getCityName());
			sb.append("',`invite_code`='" + invite);
			sb.append("',`user_name`='" + audit.getSonUserName());
			sb.append("',`last_time`=" + now_time);
			sb.append(",`regist_time`=" + now_time);
			sb.append(",`user_type_id`=").append(audit.getUserTypeId());
			sb.append(",merchant_id='").append(audit.getMerchantId()).append("'");
            sb.append(",relation_type=").append(audit.getRelationType());
            sb.append(",detail_type=").append(audit.getDetailType());
            if(audit.getDetailType().equals(1)){
                sb.append(",source_type=").append(1);
            }else{
                sb.append(",source_type=").append(2);
            }
			sql_list.add(sb.toString());

			sb.delete(0, sb.length());
			sb.append("insert into tb_user_client_detail set ");
			sb.append("`userid`='" + audit.getUserId());
			sb.append("',`user_name`='" + audit.getSonUserName());
			sb.append("',`createtime`=" + now_time);
			sb.append(",`citycode`='" + audit.getCityCode());
			sb.append("',`cityname`='" + audit.getCityName());
			sb.append("',`modifytime`=" + now_time);
			if (StringUtils.isNotBlank(audit.getIdentityCard())) {
				sb.append(",`paperscode`='").append(audit.getIdentityCard()).append("'");
				sb.append(",`identity_card`='").append(audit.getIdentityCard()).append("'");
			}
			if (StringUtils.isNotBlank(audit.getIdentityCardAddress())) {
				sb.append(",`address`='").append(audit.getIdentityCardAddress()).append("'");
				sb.append(",`identity_card_address`='").append(audit.getIdentityCardAddress()).append("'");
			}
			sb.append(",`identity_card_name`='").append(audit.getSonUserName()).append("'");
			if (StringUtils.isNotBlank(customer)) {
				sb.append(",`creator`='").append(customer).append("'");
			}
            sb.append(",`period`='").append(audit.getPeriod()).append("'");
            sb.append(",`identify_photo_head`='").append(audit.getIdentifyPhotoHead()).append("'");
            sb.append(",`identify_photo_front`='").append(audit.getIdentifyPhotoFront()).append("'");
            sb.append(",`identify_photo_reverse`='").append(audit.getIdentifyPhotoReverse()).append("'");
            sb.append(",`identify_photo_hand`='").append(audit.getIdentifyPhotoHand()).append("'");
            /**2020/4/3,卡盟侧全部开启活体识别，RXG修改开始**/
            sb.append(",`living_status`='").append(livingStatus).append("'");
            /**2020/4/3,卡盟侧全部开启活体识别，RXG修改结束**/
			sql_list.add(sb.toString());
		}

		sb.delete(0, sb.length());
		sb.append("update tb_job_number_audit set ");
		sb.append("order_status=").append(audit.getOrderStatus());
		sb.append(",audit_time=").append(now_time);
		if (2 == auditType && StringUtils.isNotBlank(customer)) {
			sb.append(",customer='").append(customer).append("'");
		}
		sb.append(",audit_type=").append(auditType);
		sb.append(",auto_audit_desc='").append(audit.getAutoAuditDesc()).append("'");
		sb.append(",auto_audit_result=").append(audit.getAutoAuditResult());
		sb.append(",gzt_check='").append(audit.getGztCheck()).append("'");
		sb.append(",remarks='").append(audit.getRemarks()).append("'");
		sb.append(" where order_id='").append(audit.getOrderId()).append("' ");
		sql_list.add(sb.toString());

		sb.delete(0, sb.length());
		sb.append("insert into tb_job_number_audit_log set ");
        Integer orderStatus = audit.getOrderStatus();
        if("1".equals(orderStatus)){
            sb.append(" status=").append(4);
        }else{
            sb.append(" status=").append(5);
        }
		if (StringUtils.isNotBlank(customer)) {
			sb.append(",customer='").append(customer).append("'");
		}
		sb.append(",order_id='").append(audit.getOrderId()).append("' ");
		sb.append(",create_time='").append(now_time).append("' ");
		sql_list.add(sb.toString());

		int[] result = executeBatch(sql_list);
		return result != null && result.length > 0;
	}

	@Override
	public String getInviteCode() {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			final String key = RedisConstant.INVITE;
			StringBuilder sb = new StringBuilder(256);
			sb.append("select id from tb_user_client where invite_code=? limit 1");
			boolean getNew = true;
			String inviteCode = "";
			while (getNew) {
				inviteCode = StringUtil.randomString(6);
				if (ObjectUtils.nonNullOrEmpty(redis_hget(key, inviteCode))) {
					continue;
				}
				redis_hset(key, inviteCode, "1");
				getNew = session.executeQuery(sb.toString(), rs -> (rs != null && rs.next()), inviteCode);
			}
			return inviteCode;
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public Merchant getDealerInfoByInviteCode(String inviteCode) {
		if (6 != inviteCode.length() && 8 != inviteCode.length()) {
			return null;
		}
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sb = new StringBuilder();
			if (6 == inviteCode.length()) {
				sb.append("select a.source_type,a.dealer_id,b.company_name as dealer_name,b.merchant_attribute");
				sb.append(" from tb_user_client a");
				sb.append(" left join tb_merchant b on a.dealer_id=b.dealer_id");
				sb.append(" where a.invite_code='");
				sb.append(inviteCode).append("' limit 1");
			} else if (8 == inviteCode.length()) {
				sb.append("select 0 source_type, dealer_id,dealer_name,1 as merchant_attribute");
				sb.append(" from tb_user_referral where referral_code='");
				sb.append(inviteCode).append("' limit 1");
			}
			return session.executeQuery(sb.toString(), rs -> {
				if (rs != null && rs.next()) {
					Merchant m = new Merchant();
					m.setSourceType(rs.getInt("source_type"));
					m.setDealerId(rs.getString("dealer_id"));
					m.setCompanyName(rs.getString("dealer_name"));
					m.setMerchant_attribute(rs.getInt("merchant_attribute"));
					return m;
				}
				log.warn("db not found info,sql:" + sb.toString());
				return null;
			});
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public boolean setVisitor(SelfRegistering s) {
		List<String> sql_list = new ArrayList<String>();
		StringBuilder sb = new StringBuilder();
		sb.append("insert into tb_merchant set ");
		sb.append("`user_type_id`=").append(s.getUser_type_id());
		sb.append(",`dealer_id`='").append(s.getDealer_id());
		sb.append("',`company_name`='").append(s.getCompany_name());
		sb.append("',`createtime`=").append(s.getCreateTime());
		sb.append(",`merchant_type`=").append(s.getMerchantType());
		sb.append(",`status`=").append(s.getStatus());
		sb.append(",`modify_time`=").append(s.getCreateTime());
		sb.append(",`super_dealer_id`='").append(s.getSuper_dealer_id()).append("'");
		sb.append(",`super_dealer_name`='").append(s.getSuper_dealer_name()).append("'");
		sb.append(",`super_invite_code`='").append(s.getSupper_invite_code()).append("'");
		sb.append(",`merchant_id`='").append(s.getMerchantId()).append("'");
		sb.append(",`ytsk_sub_merchantids`=''");
        if ("1".equals(s.getOperationType())) {
            sb.append(",`detail_type`=").append(s.getDetailType());
            sb.append(",`source_type`=").append(s.getSourceType());
            sb.append(",`business_licence`='").append(s.getIdCard()).append("'");
            sb.append(",`address`='").append(s.getIdentityCardAddress()).append("'");
        }
		sql_list.add(sb.toString());

		sb.delete(0, sb.length());
		sb.append("insert into tb_merchant_account set `dealer_id`='");
		sb.append(s.getDealer_id());
		sb.append("',`update_date`='" + DateUtils.formatDateByFormat(s.getCreateTime(), "yyyyMMdd"));
		sb.append("'");
		sb.append(",`merchant_id`='").append(s.getMerchantId()).append("'");
		sql_list.add(sb.toString());

		sb.delete(0, sb.length());
		sb.append("insert into tb_user_account set `user_id`='").append(s.getUser_id()).append("'");
		sql_list.add(sb.toString());

		sb.delete(0, sb.length());
		sb.append("insert into tb_user_client set ");
		sb.append("`userid`='").append(s.getUser_id());
		sb.append("',`phone`='").append(s.getPhone());
		sb.append("',`dealer_id`='").append(s.getDealer_id());
		sb.append("',`createtime`=").append(s.getCreateTime());
		sb.append(",`user_name`='").append(s.getUser_name());
		sb.append("',`last_time`=").append(s.getCreateTime());
		sb.append(",`citycode`='").append(s.getCityCode());
		sb.append("',`cityname`='").append(s.getCityName());
		sb.append("',`user_type_id`=").append(s.getUser_type_id()).append(",`is_main`=").append(s.getIs_main())
				.append(",`tmsi`=").append(s.getCreateTime()).append(",`osversion`='").append(s.getOsversion())
				.append("',`appversion`='").append(s.getAppversion()).append("',`ostype`='").append(s.getOstype())
				.append("',`phonetype`='").append(s.getPhonetype()).append("',`net`='").append(s.getNet())
				.append("',`applicationID`='").append(s.getApplicationID()).append("',`host_ip`='")
				.append(s.getHost_ip()).append("',`longitude`='").append(s.getLongitude()).append("',`latitude`='")
				.append(s.getLatitude()).append("'");
		sb.append(",`merchant_id`='").append(s.getMerchantId()).append("'");
        if ("1".equals(s.getOperationType())) {
            sb.append(",`detail_type`=").append(s.getDetailType());
            sb.append(",`source_type`=").append(s.getSourceType());
        }
		sql_list.add(sb.toString());

		sb.delete(0, sb.length());
		sb.append("insert into tb_user_client_detail set ");
		sb.append("`userid`='").append(s.getUser_id());
		sb.append("',`user_name`='").append(s.getUser_name());
		sb.append("',`citycode`='").append(s.getCityCode());
		sb.append("',`cityname`='").append(s.getCityName());
		sb.append("',`createtime`=").append(s.getCreateTime());
		sb.append(",`user_type_id`=").append(s.getUser_type_id());
		sb.append(",`modifytime`=").append(s.getCreateTime());
        if (Objects.nonNull(s.getLivingCheckFlag())) {
            sb.append(",`living_status`=").append(s.getLivingCheckFlag());
        }
		sql_list.add(sb.toString());

		/** 运营平台消息 **/
		sb.delete(0, sb.length());
		sb.append("用户");
		sb.append(s.getPhone().substring(0, 3)).append("********").append("于")
				.append(DateUtils.format(s.getCreateTime(), "yyyy-MM-dd HH:mm:ss")).append("注册成功");
		final String content = sb.toString();
		sb.delete(0, sb.length());

		sb.append("insert into tb_message_notice set ");
		sb.append("msg_id='ECSMSG_").append(StringUtil.randomString(7)).append("'");
		sb.append(",msg_type=").append(Constant.MsgType.TYPE6);
		sb.append(",create_time=").append(s.getCreateTime());
		sb.append(",exp_time=").append(s.getCreateTime() + 2 * 60 * 60 * 1000);
		sb.append(",content='").append(content).append("'");
		sql_list.add(sb.toString());
		/** 运营平台消息 **/
		int[] result = executeBatch(sql_list);
		log.debug("setVisitor sql result:" + Arrays.toString(result));
		return result != null && result.length > 0;
	}

	@Override
	public DeviceIdInfo getDeviceIdInfo(String deviceid) {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sb = new StringBuilder(256);
			sb.append("select kmstate,DealerID,Mac,DealerName,Attribute,isp,province_name");
			sb.append(",is_manage");
			sb.append(" from ib_kameng_deliveryrecord");
			sb.append(" where `KMRecordCode`='").append(deviceid).append("'");
			return session.executeQuery(sb.toString(), rs -> {
				if (rs != null && rs.next()) {
					DeviceIdInfo d = new DeviceIdInfo();
					d.setSuper_dealerid(rs.getString("DealerID"));
					d.setState(rs.getInt("kmstate"));
					d.setMac(rs.getString("Mac"));
					d.setDealer_name(rs.getString("DealerName").replaceAll("[\r|\n]", ""));
					d.setAttribute(rs.getInt("Attribute"));
					d.setIsp(rs.getInt("isp"));
					d.setProvinceName(rs.getString("province_name"));
					d.setIsManager(rs.getInt("is_manage"));
					return d;
				}
				log.warn("设备未找到：" + deviceid);
				return null;
			});
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public SelfRegistering getVisitorInfo(String dealerId) {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sb = new StringBuilder();
			sb.append("select status,popchannel,super_invite_code,createtime,popchannel_name");
			sb.append(",super_dealer_id,super_dealer_name,super_invite_code");
			sb.append(",company_name,merchant_type,business_licence");
			sb.append(",(select user_name from tb_user_client where dealer_id=tb_merchant.dealer_id");
			sb.append(" and is_main=1 limit 1) user_name ");
			sb.append(" from tb_merchant ");
			sb.append(" where dealer_id='").append(dealerId).append("'");
			return session.executeQuery(sb.toString(), rs -> {
				if (rs != null && rs.next()) {
					SelfRegistering s = new SelfRegistering();
					s.setCreateTime(rs.getLong("createtime"));
					s.setStatus(rs.getInt("status"));
					s.setPopchannel(rs.getString("popchannel"));
					s.setSupper_invite_code(rs.getString("super_invite_code"));
					s.setPopchannel_name(rs.getString("popchannel_name"));
					s.setSuper_dealer_id(rs.getString("super_dealer_id"));
					s.setSuper_dealer_name(rs.getString("super_dealer_name"));
					s.setCompany_name(rs.getString("company_name"));
					s.setMerchantType(rs.getInt("merchant_type"));
					s.setIdCard(rs.getString("business_licence"));
					s.setUser_name(rs.getString("user_name"));
					return s;
				}
				log.warn("db not found,dealerId=" + dealerId);
				return null;
			});
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public boolean addRegistMerchant(SelfRegistering s) {
		List<String> sql_list = new ArrayList<>();
		StringBuilder sb = new StringBuilder();
		sb.append("insert into tb_registor_audit set ");
		sb.append("`order_id`='").append(s.getOrderId());
		sb.append("',`request_phone`='").append(s.getPhone());
		sb.append("',`user_id`='").append(s.getUser_id());
		sb.append("',`company_name`='").append(s.getCompany_name()).append("'");
		if (2 == s.getMerchantType())
			sb.append(",`address`='").append(s.getAddress()).append("'");
		sb.append(",`merchant_type`=").append(s.getMerchantType());
		sb.append(",`sign_picture`='").append(s.getImgNo4());
		sb.append("',`door_picture_left`='").append(s.getImgNo1());
		sb.append("',`door_picture_right`='").append(s.getImgNo2());
		sb.append("',`hand_picture`='").append(s.getImgNo3());
		sb.append("',`business_licence`='").append(s.getIdCard());
		sb.append("',`devMac`='").append(s.getKMRecordCode());
		sb.append("',`super_invite_code`='").append(s.getSupper_invite_code());
		sb.append("',`sign_merge_picture`='").append(s.getSignMergePicture()).append("'");
		if (ObjectUtils.nonNull(s.getDevMacAddress()))
			sb.append(",`devMac_address`='").append(s.getDevMacAddress()).append("'");
		sb.append(",`popchannel`='").append(s.getPopchannel()).append("'");
		sb.append(",`super_dealer_id`='").append(s.getSuper_dealer_id()).append("'");
		sb.append(",`popchannel_name`='").append(s.getPopchannel_name()).append("'");
		sb.append(",`super_dealer_name`='").append(s.getSuper_dealer_name()).append("'");
		sb.append(",`merchant_attribute`=").append(s.getMerchant_attribute());
		sb.append(",`discount_model`='").append(s.getDiscountModel()).append("'");
		sb.append(",`phone_model`='").append(s.getPhoneModel()).append("'");
		sb.append(",`transfer_model`='").append(s.getTransterModel()).append("'");
		sb.append(",`pack_model`='").append(s.getPackModel()).append("'");
		sb.append(",`dwk_model`='").append(s.getDwkModel()).append("'");
		sb.append(",`user_name`='").append(s.getUser_name()).append("'");
		sb.append(",`alipay_user_id`='").append(s.getAlipay_user_id()).append("'");
		sb.append(",`weixin_user_id`='").append(s.getWeixin_user_id()).append("'");
		sb.append(",`city_code`='").append(s.getCityCode()).append("'");
		sb.append(",`city_name`='").append(s.getCityName()).append("'");
		sb.append(",`before_status`=").append(s.getStatus());
		sb.append(",`create_time`=").append(s.getModifyTime());
		sb.append(",`order_status`=5");
		sb.append(",`dealer_id`='").append(s.getDealer_id()).append("'");
		sb.append(",`period`='").append(s.getPeriod()).append("'");
		sb.append(",`head_picture`='").append(s.getHeadPicture()).append("'");
		sb.append(",`store_address`='").append(s.getStoreAddress()).append("'");
		sb.append(",`scope_id`='").append(s.getScopeId()).append("'");
		if (ObjectUtils.nonNullOrEmpty(s.getSuperSuperDealerId()))
			sb.append(",`super_super_dealer_id`='").append(s.getSuperSuperDealerId()).append("'");
		if (ObjectUtils.nonNullOrEmpty(s.getSuperSuperDealerName()))
			sb.append(",`super_super_dealer_name`='").append(s.getSuperSuperDealerName()).append("'");
		sb.append(",`is_manage`=").append(s.getIsManage());
		sb.append(",`source_type`=").append(s.getSourceType());
		sb.append(",`white_card_pay`=").append(s.getWhiteCardPay());
		sb.append(",`white_card_return`=").append(s.getWhiteCardReturn());
		sb.append(",`detail_type`=").append(s.getDetailType());
		if (StringUtils.isNotBlank(s.getShopAddress())) {
			sb.append(",`shop_address`='").append(s.getShopAddress()).append("'");
		}
		if (StringUtils.isNotBlank(s.getShopSpecificAddress())) {
			sb.append(",`shop_specific_address`='").append(s.getShopSpecificAddress()).append("'");
		}
		if (StringUtils.isNotBlank(s.getIdentityCard())) {
			sb.append(",`identityCard`='").append(s.getIdentityCard()).append("'");
		}
		if (StringUtils.isNotBlank(s.getIdentityCardName())) {
			sb.append(",`identityCardName`='").append(s.getIdentityCardName()).append("'");
		}
		if (StringUtils.isNotBlank(s.getIdentityCardAddress())) {
			sb.append(",`identityCardAddress`='").append(s.getIdentityCardAddress()).append("'");
		}
		if (StringUtils.isNotBlank(s.getIdentifyPhotoHead())) {
			sb.append(",`identify_photo_head`='").append(s.getIdentifyPhotoHead()).append("'");
		}
		if (StringUtils.isNotBlank(s.getIdentifyPhotoFront())) {
			sb.append(",`identify_photo_front`='").append(s.getIdentifyPhotoFront()).append("'");
		}
		if (StringUtils.isNotBlank(s.getIdentifyPhotoReverse())) {
			sb.append(",`identify_photo_reverse`='").append(s.getIdentifyPhotoReverse()).append("'");
		}
		if (StringUtils.isNotBlank(s.getIdentifyPhotoHand())) {
			sb.append(",`identify_photo_hand`='").append(s.getIdentifyPhotoHand()).append("'");
		}
        sb.append(",`sdk_user_id`='").append(s.getSdkUserId()).append("'");
        sb.append(",`operation_type`='").append(s.getOperationType()).append("'");
        sb.append(",`app_id`='").append(s.getAppId()).append("'");
		sql_list.add(sb.toString());

		sb.delete(0, sb.length());
		sb.append("update ib_kameng_deliveryrecord set ");
		sb.append("`kmstate`='").append(2);
		sb.append("' where `KMRecordCode`='").append(s.getKMRecordCode()).append("'");
		sql_list.add(sb.toString());

		sb.delete(0, sb.length());
		sb.append("insert into tb_registor_audit_log set ");
		sb.append("order_id='").append(s.getOrderId()).append("'");
		sb.append(",create_time=").append(s.getModifyTime());
		sb.append(",status=1");
		sql_list.add(sb.toString());

        sb.delete(0, sb.length());
        sb.append("update tb_merchant set ");
        sb.append("`status`=3");
        sb.append(" where dealer_id='").append(s.getDealer_id()).append("'");

		sql_list.add(sb.toString());
		int result[] = executeBatch(sql_list);
		return result != null && result.length > 0;
	}

	@Override
	public boolean activeMerchant(SelfRegistering s, String orderId, Map<String, String> baseMap, int detailType) {
		List<String> sql_list = new ArrayList<String>();
		StringBuilder sb = new StringBuilder();
		sb.append("update tb_merchant set ");
		sb.append("`dealer_id`='").append(s.getDealer_id());
		sb.append("',`company_name`='").append(s.getCompany_name());
		sb.append("',`address`='").append(s.getAddress());
		sb.append("',`merchant_type`=").append(s.getMerchantType());
		sb.append(",`status`=").append(s.getStatus());
		sb.append(",`modify_time`=").append(s.getModifyTime());
		sb.append(",`isSignAgreement`=").append(1);
		sb.append(",`sign_time`=").append(s.getSignTime());
		sb.append(",`sign_picture`='").append(s.getImgNo4());
		sb.append("',`door_picture_left`='").append(s.getImgNo1());
		sb.append("',`door_picture_right`='").append(s.getImgNo2());
		sb.append("',`hand_picture`='").append(s.getImgNo3());
		sb.append("',`business_licence`='").append(s.getIdCard());
		sb.append("',`devMac`='").append(s.getKMRecordCode());
		sb.append("',`super_invite_code`='").append(s.getSupper_invite_code());
		sb.append("',`regist_time`=").append(s.getModifyTime());
		sb.append(",`sign_merge_picture`='").append(s.getSignMergePicture()).append("'");
		sb.append(",`devMac_address`='").append(s.getDevMacAddress()).append("'");
		sb.append(",`popchannel`='").append(s.getPopchannel()).append("'");
		sb.append(",`super_dealer_id`='").append(s.getSuper_dealer_id()).append("'");
		sb.append(",`popchannel_name`='").append(s.getPopchannel_name()).append("'");
		sb.append(",`super_dealer_name`='").append(s.getSuper_dealer_name()).append("'");
		// 维持merchant_attribute有ib_kameng_deliveryrecord中isp运营商类型，现只支持1远特2联通
		sb.append(",`merchant_attribute`=").append(s.getMerchant_attribute() > 2 ? 4 : s.getMerchant_attribute());
		sb.append(",`discount_model`='").append(s.getDiscountModel()).append("'");
		sb.append(",`phone_model`='").append(s.getPhoneModel()).append("'");
		sb.append(",`pack_model`='").append(s.getPackModel()).append("'");
		sb.append(",`dwk_model`='").append(s.getDwkModel()).append("'");
		sb.append(",`city_code`='").append(s.getCityCode()).append("'");
		sb.append(",`transfer_model`='").append(s.getTransterModel()).append("'");
		sb.append(",`period`='").append(s.getPeriod()).append("'");
		sb.append(",`head_picture`='").append(s.getHeadPicture()).append("'");
		if (StringUtils.isNotBlank(s.getShopAddress()) && StringUtils.isNotBlank(s.getShopSpecificAddress())) {
			sb.append(",`store_address`='").append(s.getShopAddress() + s.getShopSpecificAddress()).append("'");
		} else {
			sb.append(",`store_address`='").append(s.getStoreAddress()).append("'");
		}
		if (ObjectUtils.nonNullOrEmpty(s.getSuperSuperDealerId()))
			sb.append(",`super_super_dealer_id`='").append(s.getSuperSuperDealerId()).append("'");
		if (ObjectUtils.nonNullOrEmpty(s.getSuperSuperDealerName()))
			sb.append(",`super_super_dealer_name`='").append(s.getSuperSuperDealerName()).append("'");
		sb.append(",`is_manage`=").append(s.getIsManage());
		sb.append(",`check_location_flag`=").append(s.getCheckLocationFlag());
		sb.append(",`check_location`=").append(s.getCheckLocation());
		sb.append(",`user_region`='").append(s.getShopAddress()).append("'");

		// 2018-10-25 白卡制卡费相关
		sb.append(",`white_card_pay`=").append(s.getWhiteCardPay());
		sb.append(",`white_card_return`=").append(s.getWhiteCardReturn());
		// 2018-10-25 白卡制卡费相关
		// 喜牛默认为白金用户
		if (ObjectUtils.nonNullOrEmpty(baseMap)) {
			sb.append(",base_success_frequency=").append(baseMap.get(OtherConfType.BASE_SUCCESS_NUM.getConfName()));
			sb.append(",base_total_frequency=").append(baseMap.get(OtherConfType.BASE_TOTAL_NUM.getConfName()));
			sb.append(",base_total_score=").append(baseMap.get(OtherConfType.BASE_TOTAL_score.getConfName()));
		}

		sb.append(" where dealer_id='").append(s.getOldDealerId()).append("'");
		sql_list.add(sb.toString());

		sb.delete(0, sb.length());
		sb.append("update tb_user_client set ");
		sb.append("`dealer_id`='").append(s.getDealer_id());
		sb.append("',`user_name`='").append(s.getUser_name()).append("'");
		if (ObjectUtils.nonNullOrEmpty(s.getAlipay_user_id()))
			sb.append(",`alipay_user_id`='").append(s.getAlipay_user_id()).append("'");
		if (ObjectUtils.nonNullOrEmpty(s.getWeixin_user_id()))
			sb.append(",`weixin_user_id`='").append(s.getWeixin_user_id()).append("'");
		sb.append(",`citycode`='").append(s.getCityCode());
		sb.append("',`cityname`='").append(s.getCityName()).append("'");
		if (ObjectUtils.nonNullOrEmpty(s.getInvite_code()))
			sb.append(",`invite_code`='").append(s.getInvite_code()).append("'");
		sb.append(",`last_time`=").append(s.getModifyTime());
		sb.append(",`regist_time`=").append(s.getModifyTime());
		sb.append(" where `userid`='").append(s.getUser_id()).append("'");
		sql_list.add(sb.toString());

		// if (detailType == 2) {
		// int result[] = DbConnectionUtils.executeBatch(sql_list);
		// if (Objects.isNull(result) || result.length == 0) {
		// return false;
		// }
		// }

		// 继承popchannel的售卡权限
		sb.delete(0, sb.length());
		sb.append("INSERT INTO tb_merchant_scope_config SET dealer_id = '").append(s.getDealer_id()).append("', ");
		sb.append("scope_id ='").append(s.getScopeId()).append("'");
		sb.append(",create_time=").append(s.getModifyTime());
		if (s.getType() == 1) {// 远特卡全国可开卡
			sb.append(",is_local=").append(0);
			sb.append(",cus_on_off=").append(0);
		} else {
			sb.append(",is_local=").append(1);
			sb.append(",cus_on_off=").append(1);
		}
		sql_list.add(sb.toString());

		sb.delete(0, sb.length());
		sb.append("update tb_merchant_account set `dealer_id`='");
		sb.append(s.getDealer_id());
		sb.append("' where dealer_id='").append(s.getOldDealerId()).append("'");
		sql_list.add(sb.toString());

		sb.delete(0, sb.length());
		sb.append("update tb_user_client_detail set ");
		sb.append("`citycode`='").append(s.getCityCode());
		sb.append("',`cityname`='").append(s.getCityName());
		sb.append("',`user_name`='").append(s.getUser_name()).append("'");
		if (StringUtils.isNotBlank(s.getIdentityCard())) {
			sb.append(",`paperscode`='").append(s.getIdentityCard()).append("'");
		} else {
			sb.append(",`paperscode`='").append(s.getIdCard()).append("'");
		}
		if (s.getMerchantType() == 2) {
			sb.append(",`address`='").append(s.getAddress()).append("'");
		}
		sb.append(",`devMac`='").append(s.getKMRecordCode()).append("'");
		sb.append(",`modifytime`=").append(s.getModifyTime());
		if (StringUtils.isNotBlank(s.getIdentifyPhotoHand())) {
			sb.append(",`identify_photo_hand`='").append(s.getIdentifyPhotoHand()).append("'");
		}
		if (StringUtils.isNotBlank(s.getIdentifyPhotoHead())) {
			sb.append(",`identify_photo_head`='").append(s.getIdentifyPhotoHead()).append("'");
		}
		if (StringUtils.isNotBlank(s.getIdentifyPhotoFront())) {
			sb.append(",`identify_photo_front`='").append(s.getIdentifyPhotoFront()).append("'");
		}
		if (StringUtils.isNotBlank(s.getIdentifyPhotoReverse())) {
			sb.append(",`identify_photo_reverse`='").append(s.getIdentifyPhotoReverse()).append("'");
		}
		if (StringUtils.isNotBlank(s.getPeriod())) {
			sb.append(",`period`='").append(s.getPeriod()).append("'");
		}
		if (StringUtils.isNotBlank(s.getIdentityCard())) {
			sb.append(",`identity_card`='").append(s.getIdentityCard()).append("'");
		}
		if (StringUtils.isNotBlank(s.getIdentityCardName())) {
			sb.append(",`identity_card_name`='").append(s.getIdentityCardName()).append("'");
		}
		if (StringUtils.isNotBlank(s.getIdentityCardAddress())) {
			sb.append(",`identity_card_address`='").append(s.getIdentityCardAddress()).append("'");
		}
        /**2020/4/3,卡盟侧全部开启活体识别，RXG修改开始**/
        int detailType1 = s.getDetailType();
        log.info("卡盟侧全部开启活体识别，RXG修改开始,detailType1 = " + detailType1);
        log.info("卡盟侧全部开启活体识别，RXG修改开始,detailType = " + detailType);
        if (1 == detailType1) {
            log.info("卡盟侧全部开启活体识别,living_status = " + Constant.KAMENG_LIVING_STATUS_INIT_VALUE);
            sb.append(",`living_status`='").append(Constant.KAMENG_LIVING_STATUS_INIT_VALUE).append("'");
        }
        /**2020/4/3,卡盟侧全部开启活体识别，RXG修改结束**/
		sb.append(" where `userid`='").append(s.getUser_id()).append("'");
		sql_list.add(sb.toString());

		sb.delete(0, sb.length());
		sb.append("update tb_openemptycard_order set ");
		sb.append("`dealer_id`='").append(s.getDealer_id());
		sb.append("' where `dealer_id`='").append(s.getOldDealerId()).append("'");
		sql_list.add(sb.toString());

		sb.delete(0, sb.length());
		sb.append("update ib_kameng_deliveryrecord set ");
		sb.append("`kmstate`='").append(1);
		sb.append("',`ActivationTime`='").append(DateUtils.format(s.getSignTime(), "yyyy-MM-dd HH:mm:ss"));
		sb.append("' where `KMRecordCode`='").append(s.getKMRecordCode()).append("'");
		sql_list.add(sb.toString());

		sb.delete(0, sb.length());
		sb.append("update tb_user_account_detail set `dealer_id`='");
		sb.append(s.getDealer_id());
		sb.append("' where dealer_id='").append(s.getOldDealerId()).append("'");
		sql_list.add(sb.toString());

		sb.delete(0, sb.length());
		sb.append("update tb_recharge_phone_order set `dealer_id`='");
		sb.append(s.getDealer_id());
		sb.append("' where dealer_id='").append(s.getOldDealerId()).append("'");
		sql_list.add(sb.toString());

		sb.delete(0, sb.length());
		sb.append("update tb_recharge_flow_order set `dealer_id`='");
		sb.append(s.getDealer_id());
		sb.append("' where dealer_id='").append(s.getOldDealerId()).append("'");
		sql_list.add(sb.toString());

		sb.delete(0, sb.length());
		sb.append("update tb_rech_order set `dealer_id`='");
		sb.append(s.getDealer_id());
		sb.append("' where dealer_id='").append(s.getOldDealerId()).append("'");
		sql_list.add(sb.toString());

		sb.delete(0, sb.length());
		sb.append("update tb_deduction_detail set `dealer_id`='");
		sb.append(s.getDealer_id());
		sb.append("' where dealer_id='").append(s.getOldDealerId()).append("'");
		sql_list.add(sb.toString());

		sb.delete(0, sb.length());
		sb.append("update tb_registor_audit set ");
		sb.append("merchant_status=1");
		sb.append(",dealer_id='").append(s.getDealer_id()).append("'");
		sb.append(" where order_id='").append(orderId).append("'");
		sb.append(" limit 1");
		sql_list.add(sb.toString());

		sb.delete(0, sb.length());
		sb.append("update tb_advice_feedback set ");
		sb.append("dealer_id='").append(s.getDealer_id()).append("'");
		sb.append(",`user_name`='").append(s.getUser_name()).append("'");
		sb.append(",`company_name`='").append(s.getCompany_name()).append("'");
		sb.append(" where dealer_id='").append(s.getOldDealerId()).append("'");
		sql_list.add(sb.toString());

		/** 运营平台消息 **/
		sb.delete(0, sb.length());
		sb.append("商户");
		sb.append(s.getCompany_name().substring(0, 1)).append("**").append("于")
				.append(DateUtils.format(s.getModifyTime(), "yyyy-MM-dd HH:mm:ss")).append("绑定设备成功。");
		final String content = sb.toString();
		sb.delete(0, sb.length());

		sb.append("insert into tb_message_notice set ");
		sb.append("msg_id='ECSMSG_")
				.append(DateUtils.format(s.getModifyTime(), "yyMMddHHmm") + StringUtil.randomNumberString(6))
				.append("'");
		sb.append(",msg_type=").append(Constant.MsgType.TYPE7);
		sb.append(",create_time=").append(s.getModifyTime());
		sb.append(",exp_time=").append(s.getModifyTime() + 2 * 60 * 60 * 1000);
		sb.append(",content='").append(content).append("'");
		sql_list.add(sb.toString());
		/** 运营平台消息 **/

		/********** 授权牌初始信息 ************/
		sb.delete(0, sb.length());
		sb.append("insert into tb_merchant_shqp set ");
		sb.append("dealer_id='").append(s.getDealer_id()).append("'");
		sb.append(",modify_time=").append(s.getModifyTime());
		sb.append(",device_id='").append(s.getKMRecordCode()).append("'");
		sql_list.add(sb.toString());
		/********** 授权牌初始信息 ************/
		int result[] = executeBatch(sql_list);
		log.debug(s.getDealer_id() + ";activeMerchant sql result:" + Arrays.toString(result));
		return result != null && result.length > 0;
	}

	@Override
	public Integer checkPowerForModel(String userid) {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sb = new StringBuilder();
			sb.append("select 1 from tb_apply_model_order where user_id='").append(userid)
					.append("' and order_status=2").append(" limit 1");

			Integer r = session.executeQuery(sb.toString(), rs -> {
				return (rs != null && rs.next()) ? 3 : 0;
			});
			if (r == null) {
				return null;
			}
			if (r.equals(3)) {
				return 3;
			}
			Long time = Clock.systemDefaultZone().millis();
			Long startTime = time - model_protect_time;
			sb.delete(0, sb.length());
			sb.append("select sys_order_id from tb_apply_model_order where user_id='").append(userid).append("' ")
					.append("and order_status=1 and create_time>").append(startTime).append(" limit 1");
			return session.executeQuery(sb.toString(), rs -> {
				return (rs != null && rs.next()) ? 2 : 1;
			});
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public KamengModelConfig getPrice() {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sb = new StringBuilder();
			sb.append("select priceone,max_nums from tf_model_config limit 1");
			return session.executeQuery(sb.toString(), rs -> {
				if (rs != null && rs.next()) {
					KamengModelConfig k = new KamengModelConfig();
					k.setPrice(rs.getInt("priceone"));
					k.setMax_num(rs.getInt("max_nums"));
					return k;
				}

				log.warn("not found price,sql:" + sb.toString());
				return null;
			});
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public boolean noSuperSetInfo(SelfRegistering s) {
		StringBuilder sb = new StringBuilder();
		sb.append("insert into tb_merchant_no_super set ");
		sb.append("`dealer_id`='").append(s.getDealer_id()).append("'");
		sb.append(",`sup_dealer_id`='").append(s.getSuper_dealer_id()).append("'");
		sb.append(",`device_id`='").append(s.getKMRecordCode()).append("'");
		sb.append(",`regist_time`=").append(s.getCreateTime());
		sb.append(",`active_time`=").append(s.getModifyTime());
		sb.append(",`company_name`='").append(s.getCompany_name()).append("'");
		sb.append(",`merchant_type`=").append(s.getMerchantType());
		sb.append(",`business_licence`='").append(s.getIdCard()).append("'");
		sb.append(",`address`='").append(s.getAddress()).append("'");
		sb.append(",`phone`=").append(s.getPhone());
		return executeUpdate(sb.toString());
	}

	@Override
	public ModelInfo getDealerModel(String dealerId) {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sb = new StringBuilder();
			sb.append("select phone_model,discount_model,income_transter_model,pack_model,dwk_model");
			sb.append(",white_card_pay,white_card_return,check_location_flag,check_location");
			sb.append(",limit_mall,limit_liantong,dealer_id");
			sb.append(" from tb_user_referral ");
			sb.append(" where dealer_id=? limit 1");
			if (ObjectUtils.nonNullOrEmpty(dealerId)) {
				ModelInfo model = session.executeQuery(sb.toString(), rs -> {
					if (rs != null && rs.next()) {
						ModelInfo m = new ModelInfo();
						m.setDiscountModel(rs.getString("discount_model"));
						m.setPhoneModel(rs.getString("phone_model"));
						m.setIncomeTransterModel(rs.getString("income_transter_model"));
						m.setPackModel(rs.getString("pack_model"));
						m.setDwkModel(rs.getString("dwk_model"));
						m.setWhiteCardPay(rs.getLong("white_card_pay"));
						m.setWhiteCardReturn(rs.getLong("white_card_return"));
						m.setCheckLocationFlag(rs.getInt("check_location_flag"));
						m.setCheckLocation(rs.getInt("check_location"));
						m.setLimitMall(rs.getInt("limit_mall"));
						m.setLimitLiantong(rs.getInt("limit_liantong"));
						m.setDealerId(rs.getString("dealer_id"));
						return m;
					}
					return null;
				}, dealerId);
				if (ObjectUtils.nonNull(model))
					return model;
			}
			dealerId = "basemodel";
			return session.executeQuery(sb.toString(), rs -> {
				if (rs != null && rs.next()) {
					ModelInfo m = new ModelInfo();
					m.setDiscountModel(rs.getString("discount_model"));
					m.setPhoneModel(rs.getString("phone_model"));
					m.setIncomeTransterModel(rs.getString("income_transter_model"));
					m.setPackModel(rs.getString("pack_model"));
					m.setDwkModel(rs.getString("dwk_model"));
					m.setWhiteCardPay(rs.getLong("white_card_pay"));
					m.setWhiteCardReturn(rs.getLong("white_card_return"));
					return m;
				}
				return null;
			}, dealerId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public SelfRegistering getRegistInfo(String orderId) {
		try (ServerSqlSession session = getSqlDataSource().session()) {
			StringBuilder sb = new StringBuilder(256);
			sb.append("select ");
			sb.append("request_phone,company_name,address,merchant_type,create_time");
            sb.append(",sign_picture,door_picture_left,app_id,order_id");
			sb.append(",door_picture_right,hand_picture,business_licence");
			sb.append(",devMac,super_invite_code,sign_merge_picture,devMac_address");
			sb.append(",popchannel,super_dealer_id,popchannel_name,super_dealer_name");
			sb.append(",merchant_attribute,discount_model,phone_model");
			sb.append(",transfer_model,user_name,alipay_user_id,weixin_user_id");
			sb.append(",city_code,city_name,user_id,dealer_id,period,head_picture");
			sb.append(",store_address,scope_id,super_super_dealer_id,super_super_dealer_name");
			sb.append(",is_manage,pack_model,dwk_model,source_type");
            sb.append(",white_card_pay,white_card_return,detail_type,sdk_user_id,operation_type,detail_type");
			sb.append(",identityCard,identityCardName,identityCardAddress,shop_address,shop_specific_address");
			sb.append(",identify_photo_head,identify_photo_front,identify_photo_reverse,identify_photo_hand");
			sb.append(" from tb_registor_audit where ");
			sb.append(" order_id='").append(orderId).append("' limit 1");
			return session.executeQuery(sb.toString(), rs -> {
				if (rs != null && rs.next()) {
					SelfRegistering s = new SelfRegistering();
					s.setPhone(rs.getString("request_phone"));
					s.setCompany_name(rs.getString("company_name"));
					s.setAddress(rs.getString("address"));
					s.setMerchantType(rs.getInt("merchant_type"));
					s.setSignTime(rs.getLong("create_time"));
					s.setImgNo4(rs.getString("sign_picture"));
					s.setImgNo1(rs.getString("door_picture_left"));
					s.setImgNo2(rs.getString("door_picture_right"));
					s.setImgNo3(rs.getString("hand_picture"));
					s.setIdCard(rs.getString("business_licence"));
					s.setKMRecordCode(rs.getString("devMac"));
					s.setSupper_invite_code(rs.getString("super_invite_code"));
					s.setSignMergePicture(rs.getString("sign_merge_picture"));
					s.setDevMacAddress(rs.getString("devMac_address"));
					s.setPopchannel(rs.getString("popchannel"));
					s.setSuper_dealer_id(rs.getString("super_dealer_id"));
					s.setPopchannel_name(rs.getString("popchannel_name"));
					s.setSuper_dealer_name(rs.getString("super_dealer_name"));
					s.setMerchant_attribute(rs.getInt("merchant_attribute"));
					s.setDiscountModel(rs.getString("discount_model"));
					s.setPhoneModel(rs.getString("phone_model"));
					s.setTransterModel(rs.getString("transfer_model"));
					s.setUser_name(rs.getString("user_name"));
					s.setAlipay_user_id(rs.getString("alipay_user_id"));
					s.setWeixin_user_id(rs.getString("weixin_user_id"));
					s.setCityCode(rs.getString("city_code"));
					s.setCityName(rs.getString("city_name"));
					s.setUser_id(rs.getString("user_id"));
					s.setOldDealerId(rs.getString("dealer_id"));
					s.setHeadPicture(rs.getString("head_picture"));
					s.setPeriod(rs.getString("period"));
					s.setStoreAddress(rs.getString("store_address"));
					s.setScopeId(rs.getString("scope_id"));
					s.setSuperSuperDealerId(rs.getString("super_super_dealer_id"));
					s.setSuperSuperDealerName(rs.getString("super_super_dealer_name"));
					s.setIsManage(rs.getInt("is_manage"));
					s.setPackModel(rs.getString("pack_model"));
					s.setDwkModel(rs.getString("dwk_model"));
					s.setSourceType(rs.getInt("source_type"));
					s.setWhiteCardPay(rs.getLong("white_card_pay"));
					s.setWhiteCardReturn(rs.getLong("white_card_return"));
					s.setShopAddress(rs.getString("shop_address"));
					s.setShopSpecificAddress(rs.getString("shop_specific_address"));
					s.setIdentityCard(rs.getString("identityCard"));
					s.setIdentityCardName(rs.getString("identityCardName"));
					s.setIdentityCardAddress(rs.getString("identityCardAddress"));
					s.setIdentifyPhotoHead(rs.getString("identify_photo_head"));
					s.setIdentifyPhotoFront(rs.getString("identify_photo_front"));
					s.setIdentifyPhotoReverse(rs.getString("identify_photo_reverse"));
					s.setIdentifyPhotoHand(rs.getString("identify_photo_hand"));
                    s.setSdkUserId(rs.getString("sdk_user_id"));
                    s.setOperationType(rs.getString("operation_type"));
                    s.setDetailType(rs.getInt("detail_type"));
                    s.setAppId(rs.getString("app_id"));
                    s.setDealer_id(rs.getString("dealer_id"));
                    s.setOrderId(rs.getString("order_id"));
					return s;
				}
				return null;
			});
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		}
		return null;
	}

	@Override
	public Merchant getMerchantStatus(String dealerId) {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sb = new StringBuilder(128);
			sb.append("select `status`,detail_type,source_type from tb_merchant where");
			sb.append(" dealer_id=?");
			sb.append(" limit 1");
			return session.executeQuery(sb.toString(), rs -> {
				if (rs != null && rs.next()) {
					Merchant m = new Merchant();
					m.setStatus(rs.getInt("status"));
					m.setDetailType(rs.getInt("detail_type"));
					m.setSourceType(rs.getInt("source_type"));
					return m;
				}
				return null;
			}, dealerId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public boolean updateWhenAuditFail(String dealer_id, String devMac, int beforeStatus) {
		List<String> sql_list = new ArrayList<String>();
		StringBuilder sb = new StringBuilder(128);
		sb.append("update tb_merchant set ");
		sb.append("`status`=").append(beforeStatus);
		sb.append(" where dealer_id='").append(dealer_id).append("' limit 1");
		sql_list.add(sb.toString());
		sb.delete(0, sb.length());
		sb.append("update ib_kameng_deliveryrecord set ");
		sb.append("`kmstate`='").append(0);
		sb.append("' where `KMRecordCode`='").append(devMac).append("'");
		sql_list.add(sb.toString());
		int[] r = executeBatch(sql_list);
		return r != null && r.length > 0;
	}

	@Override
	public boolean auditOrder(RegistOrderInfo regisOrder) {
		List<String> sqlList = new ArrayList<String>();
		StringBuilder sb = new StringBuilder(256);
		sb.append("update tb_registor_audit set ");
		sb.append("audit_time=").append(regisOrder.getAuditTime());
		sb.append(",remarks='").append(regisOrder.getRemarks()).append("'");
		sb.append(",order_status=").append(regisOrder.getOrderStatus());
		sb.append(",customer='").append(regisOrder.getCustomer()).append("'");
		sb.append(",audit_type=").append(regisOrder.getAuditType());
		sb.append(",auto_audit_desc='").append(regisOrder.getAutoAuditDesc()).append("'");
		sb.append(",gzt_check='").append(regisOrder.getGztCheck()).append("'");
		sb.append(" where id=").append(regisOrder.getId());
		sqlList.add(sb.toString());

		sb.delete(0, sb.length());
		sb.append("insert into tb_registor_audit_log set ");
		sb.append("order_id='").append(regisOrder.getOrderId()).append("'");
		sb.append(",create_time=").append(regisOrder.getAuditTime());
		sb.append(",customer='").append("自动审核").append("'");
		if (1 == regisOrder.getOrderStatus()) {
			sb.append(",status=4");
		} else if (2 == regisOrder.getOrderStatus()) {
			sb.append(",status=5");
		} else {
			if (1 == regisOrder.getAuditType())
				sb.append(",status=6");
			else
				sb.append(",status=7");
		}
		sqlList.add(sb.toString());
		int[] res = executeBatch(sqlList);
		return res != null && res.length == 2;
	}

	@Override
	public Object[] merchantAttributeInfo(String dealerid) {
		try (ServerSqlSession session = getSqlDataSource().session()) {
			StringBuilder sb = new StringBuilder(256);
			sb.append("select b.type ");
			sb.append(" from tb_merchant_scope_config a ");
			sb.append(" left join tb_sell_scope_pool b on a.scope_id=b.scope_id");
			sb.append(" where a.dealer_id=?");
			sb.append(" group by b.type order by b.type asc");

			return session.executeQuery(sb.toString(), rs -> {
				int tot = 0;
				String attribute = "";
				if (rs != null) {
					int i = 0;
					while (rs.next()) {
						tot += rs.getInt(1);
						if (i > 0) {
							attribute += ",";
						}
						attribute += transScopeType(rs.getInt(1));
						i++;
					}
				}
				return new Object[] { tot, attribute };
			}, dealerid);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		}
		return null;
	}

	/**
	 * 1远特，2联通，3移动，4电信
	 *
	 * @param type
	 * @return
	 */
	private String transScopeType(int type) {
		String str = "";
		switch (type) {
		case 1:
			str = "远特";
			break;
		case 2:
			str = "联通";
			break;
		case 3:
			str = "移动";
			break;
		case 4:
			str = "电信";
			break;
		default:
			break;
		}
		return str;
	}

	@Override
	public List<BannerInfo> getBannerList() {
		try (ServerSqlSession session = getSqlDataSource().session()) {
			StringBuilder sb = new StringBuilder(256);
			sb.append("select banner_url,link_url,title");
			sb.append(" from tb_banner_id ");
			sb.append(" where is_default=1 and is_lock=0 ");
			sb.append(" order by power desc");
			return session.executeQuery(sb.toString(), rs -> {
				if (rs != null) {
					List<BannerInfo> list = new ArrayList<>();
					while (rs.next()) {
						BannerInfo banner = new BannerInfo();
						banner.setBanner_url(rs.getString(1));
						banner.setLink_url(rs.getString(2));
						banner.setTitle(rs.getString(3));
						list.add(banner);
					}
					return list;
				}
				return null;
			});
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		}
		return null;
	}

	@Override
	public Map<String, BannerInfo> getBannerList(String bannerIds) {
		try (ServerSqlSession session = getSqlDataSource().session()) {
			StringBuilder sb = new StringBuilder(256);
			sb.append("select banner_url,link_url,title,banner_id");
			sb.append(" from tb_banner_id ");
			sb.append(" where banner_id in (").append(StringUtil.decorateIds(bannerIds)).append(")");
			sb.append(" and is_lock=0 ");
			sb.append(" order by power desc");
			return session.executeQuery(sb.toString(), rs -> {
				if (rs != null) {
					Map<String, BannerInfo> map = new HashMap<>();
					while (rs.next()) {
						BannerInfo banner = new BannerInfo();
						banner.setBanner_url(rs.getString(1));
						banner.setLink_url(rs.getString(2));
						banner.setTitle(rs.getString(3));
						map.put(rs.getString(4), banner);
					}
					return map;
				}
				return null;
			});
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		}
		return null;
	}

	@Override
	public DeviceIdInfo macGetPromotion(String deviceid) {
		try (ServerSqlSession session = getSqlDataSource().session()) {
			StringBuilder sb = new StringBuilder(256);
			sb.append("select *");
			sb.append(" from ib_kameng_deliveryrecord");
			sb.append(" where `KMRecordCode`=? order by SysID desc limit 1");
			return session.executeQuery(sb.toString(), rs -> {
				if (rs != null && rs.next()) {
					DeviceIdInfo info = new DeviceIdInfo();
					info.setSysID(rs.getInt("SysID"));
					info.setKMRecordCode(rs.getString("KMRecordCode"));
					info.setKmstate(rs.getLong("kmstate"));
					info.setActivationTime(rs.getString("ActivationTime"));
					info.setDealerid(rs.getString("DealerID"));
					info.setDealer_name(rs.getString("DealerName"));
					info.setInsert_date(rs.getString("insert_date"));
					info.setMac(rs.getString("Mac"));
					info.setAttribute(rs.getInt("Attribute"));
					info.setIsp(rs.getInt("isp"));
					info.setProvinceName(rs.getString("province_name"));
					info.setIsFanYong(rs.getInt("if_fanyong"));
					info.setIsIndustryBusiness(rs.getString("IsIndustryBusiness"));
					info.setIsManager(rs.getInt("is_manage"));
					return info;
				}
				return null;
			}, deviceid);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public MerchantProInfo getOldAccount(String dealerId) {
		try (ServerSqlSession session = getSqlDataSource().session()) {
			StringBuilder sb = new StringBuilder(256);
			sb.append("select promotion,promotion_day,promotion_day_history,promotion_total,update_date");
			sb.append(" from tb_merchant_account");
			sb.append(" where `dealer_id`=? limit 1");
			return session.executeQuery(sb.toString(), rs -> {
				if (rs != null && rs.next()) {
					MerchantProInfo info = new MerchantProInfo();
					info.setPromotion(rs.getLong(1));
					info.setPromotionDay(rs.getLong(2));
					info.setPromotionDayHistory(rs.getLong(3));
					info.setPromotionTotal(rs.getLong(4));
					info.setUpdateDate(rs.getString(5));
					return info;
				}
				return null;
			}, dealerId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public boolean gavePromotion(MerchantProInfo info, String tbname) {
		List<String> sqlList = new ArrayList<>();
		StringBuilder sb = new StringBuilder(256);
		sb.append("insert into " + tbname + " set ");
		sb.append("date='").append(info.getDate()).append("'");
		sb.append(",dealer_id='").append(info.getDealerId()).append("'");
		sb.append(",dealer_name='").append(info.getDealerName()).append("'");
		sb.append(",type=").append(info.getType());
		sb.append(",free_day=").append(info.getFree());
		sb.append(",free_total=").append(info.getMonthFree());
		sb.append(",create_time=").append(info.getCreateTime());
		sb.append(",src_dealer_id='").append(info.getSrcDealerId()).append("'");
		sb.append(",src_dealer_name='").append(info.getSrcDealerName()).append("'");
		sqlList.add(sb.toString());

		sb.delete(0, sb.length());
		sb.append("insert into tb_merchant_account_detail set ");
		sb.append("dealer_id='").append(info.getDealerId()).append("'");
		sb.append(",dealer_name='").append(info.getDealerName()).append("'");
		sb.append(",free=").append(info.getFree());
		sb.append(",month_free=").append(info.getMonthFree());
		sb.append(",history_free=").append(info.getHistoryFree());
		sb.append(",create_time=").append(info.getCreateTime());
		sb.append(",type=").append(info.getType());
		sb.append(",date='").append(info.getDate()).append("'");
		sb.append(",src_dealer_id='").append(info.getSrcDealerId()).append("'");
		sb.append(",src_dealer_name='").append(info.getSrcDealerName()).append("'");
		sqlList.add(sb.toString());
		sb.delete(0, sb.length());
		sb.append("update tb_merchant_account set ");
		sb.append("promotion=promotion+").append(info.getPromotion());
		sb.append(",promotion_day=promotion_day+").append(info.getPromotionDay());
		sb.append(",promotion_day_history=promotion_day_history+").append(info.getPromotionDayHistory());
		sb.append(",promotion_total=promotion_total+").append(info.getPromotionTotal());
		sb.append(",update_date='").append(info.getUpdateDate()).append("'");
		sb.append(" where ");
		sb.append("dealer_id='").append(info.getDealerId()).append("' limit 1");
		sqlList.add(sb.toString());
		if (info.getType() == 7) {
			sb.delete(0, sb.length());
			sb.append("update tb_merchant SET standard_status = 1");
			sb.append(",standard_time= " + info.getDate());
			sb.append(" WHERE dealer_id='" + info.getSrcDealerId());
			sb.append("'");
			sqlList.add(sb.toString());
		}
		int[] res = executeBatch(sqlList);
		log.debug("gave promotion res:" + Arrays.toString(res));
		return (res != null && res.length > 0);
	}

	@Override
	public boolean createTable(String tbName) {
		StringBuilder sb = new StringBuilder(256);
		sb.append("CREATE TABLE if not exists `" + tbName);
		sb.append("` (`id` int(12) NOT NULL AUTO_INCREMENT,");
		sb.append("`date` varchar(8) NOT NULL DEFAULT '' COMMENT '日期yyyymmdd',");
		sb.append("`dealer_id` varchar(24) NOT NULL DEFAULT '' COMMENT '渠道id',");
		sb.append("`dealer_name` varchar(50) NOT NULL DEFAULT '' COMMENT '渠道名称',");
		sb.append("`type` varchar(2) NOT NULL DEFAULT '' COMMENT '佣金类型',");
		sb.append("`free_day` varchar(12) NOT NULL DEFAULT '0' COMMENT '日金额',");
		sb.append("`free_total` varchar(12) NOT NULL DEFAULT '0' COMMENT '日、月、历史累计金额',");
		sb.append("`create_time` varchar(13) NOT NULL DEFAULT '0' COMMENT '创建时间',");
		sb.append("`src_dealer_id` varchar(24) NOT NULL DEFAULT '' COMMENT '来源渠道id',");
		sb.append("`src_dealer_name` varchar(50) NOT NULL DEFAULT '' COMMENT '来源渠道名称',");
		sb.append(" PRIMARY KEY (`id`)");
		sb.append(") ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='商户日佣金';");
		return executeUpdate(sb.toString());
	}

	@Override
	public List<SpecialModel> specialModel(String popChannel) {
		try (ServerSqlSession session = getSqlDataSource().session()) {
			StringBuilder sb = new StringBuilder(256);
			sb.append("select model_num,model_type");
			sb.append(" from tb_mono_rule_model");
			sb.append(" where `dealer_id`=?");
			return session.executeQuery(sb.toString(), rs -> {
				List<SpecialModel> modelList = new ArrayList<>();
				if (rs != null) {
					while (rs.next()) {
						SpecialModel model = new SpecialModel();
						model.setModelNum(rs.getInt(1));
						model.setModelType(rs.getInt(2));
						modelList.add(model);
					}
				}
				return modelList;
			}, popChannel);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public List<SpecialModel> specialModelNoamral(String popChannel) {
		try (ServerSqlSession session = getSqlDataSource().session()) {
			StringBuilder sb = new StringBuilder(256);
			sb.append("select model_num");
			sb.append(" from tb_normal_rule_model");
			sb.append(" where `dealer_id`=?");
			return session.executeQuery(sb.toString(), rs -> {
				List<SpecialModel> modelList = new ArrayList<>();
				if (rs != null) {
					while (rs.next()) {
						SpecialModel model = new SpecialModel();
						model.setModelNum(rs.getInt(1));
						modelList.add(model);
					}
				}
				return modelList;
			}, popChannel);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public boolean addSpecialDealerModel(List<SpecialModel> modelList, final String dealerId, final String dealerName) {
		List<String> sqlList = modelList.stream().map(model -> {
			StringBuilder sb = new StringBuilder(128);
			sb.append("insert into tb_mono_rule_model set ");
			sb.append("dealer_id='").append(dealerId).append("'");
			sb.append(",model_num=").append(model.getModelNum());
			sb.append(",model_type=").append(model.getModelType());
			sb.append(",dealer_name='").append(dealerName).append("'");
			return sb.toString();
		}).collect(Collectors.toList());
		int[] res = executeBatch(sqlList);
		return res != null && res.length > 0;
	}

	@Override
	public boolean addSpecialDealerModelNoamal(List<SpecialModel> modelList, String dealerId, String dealerName) {
		List<String> sqlList = modelList.stream().map(model -> {
			StringBuilder sb = new StringBuilder(128);
			sb.append("insert into tb_normal_rule_model set ");
			sb.append("dealer_id='").append(dealerId).append("'");
			sb.append(",model_num=").append(model.getModelNum());
			sb.append(",dealer_name='").append(dealerName).append("'");
			return sb.toString();
		}).collect(Collectors.toList());
		int[] res = executeBatch(sqlList);
		return res != null && res.length > 0;
	}

	@Override
	public ZtgProtionModelDetail getIncomeModelDetail(DeviceIdInfo deviceInfo) {
		try (ServerSqlSession session = getSqlDataSource().session()) {
			StringBuilder sb = new StringBuilder(256);
			sb.append("select ztg_income_model");
			sb.append(" from tb_user_referral");
			sb.append(" where `dealer_id`=? limit 1");
			String ztgModel = session.executeQuery(sb.toString(), rs -> {
				if (rs != null && rs.next()) {
					return rs.getString(1);
				}
				return null;
			}, deviceInfo.getDealerid());
			if (ObjectUtils.isNullOrEmpty(ztgModel)) {
				ztgModel = Constant.BASEMODEL;
			}
			sb.delete(0, sb.length());
			sb.append("select ztg_income_model,sup_promotion,sup_sup_promotion,desc_str");
			sb.append(" from tf_ztg_promotion ");
			sb.append(" where ztg_income_model=? limit 1");
			ZtgProtionModelDetail modelDetail = session.executeQuery(sb.toString(), rs -> {
				if (rs != null && rs.next()) {
					ZtgProtionModelDetail detail = new ZtgProtionModelDetail();
					detail.setZtgIncomeModel(rs.getString(1));
					detail.setSupPromotion(rs.getInt(2));
					detail.setSupSupPromotion(rs.getInt(3));
					detail.setDescStr(rs.getString(4));
					return detail;
				}
				return null;
			}, ztgModel);
			if (modelDetail != null)
				return modelDetail;
			log.warn("未找到对应自推广返佣详情，" + ztgModel);
			ztgModel = Constant.BASEMODEL;
			return session.executeQuery(sb.toString(), rs -> {
				if (rs != null && rs.next()) {
					ZtgProtionModelDetail detail = new ZtgProtionModelDetail();
					detail.setZtgIncomeModel(rs.getString(1));
					detail.setSupPromotion(rs.getInt(2));
					detail.setSupSupPromotion(rs.getInt(3));
					detail.setDescStr(rs.getString(4));
					return detail;
				}
				return null;
			}, ztgModel);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public Map<String, String> getBuyModelInfo(String merchantId, Map<String, String> tables) {
		StringBuilder sb = new StringBuilder(256);
		sb.append("select sign_pic_url,merch_type,merch_name,call_name,buz_code");
		sb.append(" from ").append(tables.get("sk_apply_model_order"));
		sb.append(" where merchant_id=? and order_status=2 limit 1");
		try (ServerSqlSession session = getSqlDataSource().session();) {
			return session.executeQuery(sb.toString(), (rs) -> {
				if (rs != null && rs.next()) {
					Map<String, String> res = new HashMap<>();
					res.put("sign_pic_url", rs.getString("sign_pic_url"));
					res.put("merch_type", rs.getString("merch_type"));
					res.put("merch_name", rs.getString("merch_name"));
					res.put("call_name", rs.getString("call_name"));
					res.put("buz_code", rs.getString("buz_code"));
					return res;
				}
				log.info("[getBuyModelInfo]sql run error;sql:" + sb.toString());
				return Collections.emptyMap();

			}, merchantId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public boolean addMerchantDetail(String merchantId) {
		final long nowTime = Clock.systemDefaultZone().millis();
		StringBuilder sb = new StringBuilder();
		sb.append("INSERT INTO tb_merchant_detail SET ");
		sb.append("`merchant_id`='").append(merchantId).append("'");
		sb.append(",`ratify_accord_time`=").append(nowTime);
		sb.append(",`create_time`=").append(nowTime);
		return executeUpdate(sb.toString());
	}

	@Override
	public boolean updateMerchantDetail(String merchantId) {
		final long nowTime = Clock.systemDefaultZone().millis();
		StringBuilder sb = new StringBuilder();
		sb.append("update tb_merchant_detail SET ");
		sb.append("`ratify_accord_time`=").append(nowTime);
		sb.append(" where `merchant_id`='").append(merchantId).append("'");
		return executeUpdate(sb.toString());
	}

	@Override
	public MerchantDetailInfo getMerchantDetail(String merchantId) {
		StringBuilder sb = new StringBuilder();
		sb.append("select * from tb_merchant_detail where ");
		sb.append("`merchant_id`='").append(merchantId).append("'");
		sb.append(" limit 1 ");
		try (ServerSqlSession session = getSqlDataSource().session()) {
			return session.executeQuery(sb.toString(), (rs) -> {
				if (rs != null && rs.next()) {
					MerchantDetailInfo info = new MerchantDetailInfo();
					info.setId(rs.getLong("id"));
					info.setMerchantId(rs.getString("merchant_id"));
					info.setCreateTime(rs.getLong("create_time"));
					info.setRatifyAccordTime(rs.getLong("ratify_accord_time"));
					return info;
				}
				log.info("[getMerchantDetail]sql run error;sql:" + sb.toString());
				return null;
			});
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public Merchant getByDetailTypeAndIdentityCard(Integer detailType, String identityCard, String userid) {
		StringBuilder sb = new StringBuilder();
		sb.append(
				"select cd.identity_card as identityCard from tb_user_client c left join tb_user_client_detail cd on cd.userid = c.userid where ");
		sb.append(" c.detail_type=").append(detailType);
		if (StringUtils.isNotBlank(userid)) {
			sb.append(" and c.userid !='").append(userid).append("'");
		}
		sb.append(" and cd.identity_card='").append(identityCard).append("'");
		sb.append(" limit 1 ");
		try (ServerSqlSession session = getSqlDataSource().session()) {
			return session.executeQuery(sb.toString(), (rs) -> {
				if (rs != null && rs.next()) {
					Merchant merchant = new Merchant();
					merchant.setIdentityCard(rs.getString("identityCard"));
					return merchant;
				}
				log.info("[getByDetailTypeAndIdentityCard]sql run error;sql:" + sb.toString());
				return null;
			});
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public List<userClientAndRegion> getUserClient() {
		try (ServerSqlSession session = getSqlDataSource().session()) {
			StringBuilder sb = new StringBuilder(256);
			sb.append(
					"SELECT userid,dealer_id,merchant_id FROM tb_user_client WHERE dealer_id!='' and merchant_id!='' and flag=1");
			session.executeQuery(sb.toString(), rs -> {
				List<userClientAndRegion> modelList = new ArrayList<userClientAndRegion>();
				if (rs != null) {
					while (rs.next()) {
						userClientAndRegion model = new userClientAndRegion();
						model.setUserId(rs.getString("userid"));
						model.setDealerId(rs.getString("dealer_id"));
						model.setMerchantId(rs.getString("merchant_id"));
						modelList.add(model);
					}
					return modelList;
				}
				return null;
			});
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
		return null;
	}

	@Override
	public List<userClientAndRegion> getUserRegion() {
		try (ServerSqlSession session = getSqlDataSource().session()) {
			StringBuilder sb = new StringBuilder(256);
			sb.append("SELECT merchant_id,store_address FROM tb_merchant WHERE store_address !=''");
			return session.executeQuery(sb.toString(), rs -> {
				List<userClientAndRegion> modelList = new ArrayList<userClientAndRegion>();
				if (rs != null) {
					while (rs.next()) {
						userClientAndRegion model = new userClientAndRegion();
						model.setStoreAddress(rs.getString("store_address"));
						model.setMerchantId(rs.getString("merchant_id"));
						modelList.add(model);
					}
					return modelList;
				}
				return null;
			});
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public boolean updateUserClientDetail(List<userClientAndRegion> list) {
		List<String> sql_list = new ArrayList<String>();
		for (userClientAndRegion info : list) {
			StringBuilder sb = new StringBuilder(128);
			sb.append("UPDATE tb_user_client_detail SET");
			sb.append(" dealer_id='" + info.getDealerId()).append("'");
			sb.append(" ,merchant_id='" + info.getMerchantId()).append("'");
			sb.append(" WHERE userid='" + info.getUserId()).append("'");
			sql_list.add(sb.toString());
		}
		int[] r = executeBatch(sql_list);
		return r != null && r.length > 0;
	}

	@Override
	public boolean updateUserRegion(List<userClientAndRegion> list) {
		List<String> sql_list = new ArrayList<String>();
		for (userClientAndRegion info : list) {
			StringBuilder sb = new StringBuilder(128);
			sb.append("UPDATE tb_merchant SET ");
			sb.append(" user_region='" + info.getUserRegion()).append("'");
			sb.append(" WHERE merchant_id='" + info.getMerchantId()).append("'");
			sql_list.add(sb.toString());
		}
		int[] r = executeBatch(sql_list);
		return r != null && r.length > 0;
	}

	@Override
	public boolean clearTmsi() {
		String sql = "UPDATE tb_user_client SET tmsi=0";
		return executeUpdate(sql);
	}

    @Override
    public List<JobNumberAuditModel> getJobNumberAuditList(String param, Integer type) {
        try (ServerSqlSession session = getSqlDataSource().session()) {
            StringBuilder sb = new StringBuilder(256);
            sb.append("select son_user_phone,son_user_name,create_time,city_name,order_status ");
            sb.append(" from tb_job_number_audit ");
            sb.append(" where order_status IN (3,4) "); // 在途
            if (1 == type) {
                sb.append(" and merchant_id = ? ");
            } else {
                sb.append(" and son_user_phone = ? ");
            }
            sb.append(" order by create_time desc ");
            return session.executeQuery(sb.toString(), rs -> {
                List<JobNumberAuditModel> list = new ArrayList<>();
                if (rs != null) {
                    while (rs.next()) {
                        JobNumberAuditModel audit = new JobNumberAuditModel();
                        audit.setPhone(rs.getString("son_user_phone"));
                        audit.setUserName(rs.getString("son_user_name"));
                        audit.setCreateTime(rs.getString("create_time"));
                        audit.setCityName(rs.getString("city_name"));
                        audit.setOrderStatus("正在审核中");
                        list.add(audit);
                    }
                }
                return list;
            }, param);
        } catch (Exception ex) {
            log.error(ex.getMessage(), ex);
            return null;
        }
    }

    @Override
    public List<JobNumberAuditModel> getJobNumberAuditListByIdCard(String idCard) {
        try (ServerSqlSession session = getSqlDataSource().session()) {
            StringBuilder sb = new StringBuilder(256);
            sb.append("select son_user_phone,son_user_name,create_time,city_name,order_status ");
            sb.append(" from tb_job_number_audit ");
            sb.append(" where identityCard = ? ");
            sb.append(" and order_status IN (3,4) ");
            sb.append(" order by create_time desc ");
            return session.executeQuery(sb.toString(), rs -> {
                List<JobNumberAuditModel> list = new ArrayList<>();
                if (rs != null) {
                    while (rs.next()) {
                        JobNumberAuditModel audit = new JobNumberAuditModel();
                        audit.setPhone(rs.getString("son_user_phone"));
                        audit.setUserName(rs.getString("son_user_name"));
                        audit.setCreateTime(rs.getString("create_time"));
                        audit.setCityName(rs.getString("city_name"));
                        audit.setOrderStatus(rs.getString("order_status"));
                        list.add(audit);
                    }
                }
                return list;
            }, idCard);
        } catch (Exception ex) {
            log.error(ex.getMessage(), ex);
            return null;
        }
    }

    @Override
    public UserDetailModel getUserDetailList(String userId) {
        try (ServerSqlSession session = getSqlDataSource().session()) {
            StringBuilder sb = new StringBuilder(256);
            sb.append("select c.is_main,a.create_time,c.user_name,c.phone,c.cityname,a.relation_type,c.createtime,a.audit_time,d.identity_card ");
            sb.append(",d.identity_card_address,d.period,a.sign_picture,t.relation_name ");
            sb.append(" from tb_user_client c left join tb_job_number_audit a on a.user_id=c.userid ");
            sb.append(" left join tb_user_client_detail d on d.userid = c.userid ");
            sb.append(" left join tb_relation_type t on t.relation_key = a.relation_type ");
            sb.append(" where c.userid = ? ");
            sb.append(" order by c.createtime desc limit 1 ");
            return session.executeQuery(sb.toString(), rs -> {
                UserDetailModel model = new UserDetailModel();
                if (rs != null && rs.next()) {
                    model.setPhone(rs.getString("c.phone"));
                    model.setUserName(rs.getString("c.user_name"));
                    if (StringUtils.isBlank(rs.getString("a.create_time"))) {
                        model.setCreateTime(rs.getString("c.createtime"));
                    } else {
                        model.setCreateTime(rs.getString("a.create_time"));
                    }
                    model.setCityName(rs.getString("c.cityname"));
                    model.setRelationDesc(rs.getString("t.relation_name"));
                    model.setAuditTime(rs.getString("a.audit_time"));
                    model.setIdentityCard(rs.getString("d.identity_card"));
                    model.setIdentityCardAddress(rs.getString("d.identity_card_address"));
                    model.setPeriod(rs.getString("d.period"));
                    model.setSignPicture(rs.getString("a.sign_picture"));
                }
                return model;
            }, userId);
        } catch (Exception ex) {
            log.error(ex.getMessage(), ex);
            return null;
        }
    }

	@Override
	public List<ManageLogModel> getManageLogList(String dealerId) {
		try (ServerSqlSession session = getSqlDataSource().session()) {
			StringBuilder sb = new StringBuilder(256);
            sb.append("select audit_type,auto_audit_result,create_time,son_user_name,city_name,son_user_phone,order_status,auto_audit_desc,remarks ");
			sb.append(" from tb_job_number_audit ");
			sb.append(" where dealer_id=? order by create_time desc ");

			List<ManageLogModel> logList = new ArrayList<>();
			session.executeQuery(sb.toString(), rs -> {
				if (rs != null) {
					while (rs.next()) {
						ManageLogModel model = new ManageLogModel();
						model.setPhone(rs.getString("son_user_phone"));
						model.setUserName(rs.getString("son_user_name"));
						model.setCreateTime(rs.getString("create_time"));
						model.setCityName(rs.getString("city_name"));
						model.setOrderStatus(rs.getString("order_status"));
                        if (!rs.getString("order_status").equals("3") && !rs.getString("order_status").equals("4")) {
                            if (1 == rs.getInt("audit_type")) {
                                model.setMsgCont(rs.getString("auto_audit_desc"));
                            } else {
                                model.setMsgCont(rs.getString("remarks"));
                            }
                        }
						logList.add(model);
					}
				}
				return null;
			}, dealerId);

			sb.delete(0, sb.length());
			sb.append("select createtime,user_name,cityname,phone ");
			sb.append(" from tb_user_removed ");
			sb.append(" where dealer_id=? order by createtime desc ");
			session.executeQuery(sb.toString(), rs -> {
				if (rs != null) {
					while (rs.next()) {
						ManageLogModel model = new ManageLogModel();
						model.setPhone(rs.getString("phone"));
						model.setUserName(rs.getString("user_name"));
						model.setCreateTime(rs.getString("createtime"));
						model.setCityName(rs.getString("cityname"));
						model.setOrderStatus("5"); // 已删除
						logList.add(model);
					}
				}
				return null;
			}, dealerId);

			return logList;
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	public boolean addJobNumberAudit(User user, JobNumberAudit audit) {
		List<String> sql_list = new ArrayList<>();
		StringBuilder sb = new StringBuilder();
		sb.append("insert into tb_job_number_audit set ");
		sb.append("order_id='").append(audit.getOrderId()).append("'");
		sb.append(",user_id='").append(audit.getUserId()).append("'");
		sb.append(",create_time=").append(audit.getCreateTime());
		sb.append(",source_type=").append(audit.getSourceType());
		sb.append(",detail_type=").append(user.getDetailType());
		sb.append(",company_name='").append(audit.getSonUserName()).append("'");
		sb.append(",dealer_id='").append(user.getDealerId()).append("'");
		sb.append(",merchant_type=").append(audit.getMerchantType());
		sb.append(",main_user_name='").append(user.getUserName()).append("'");
		sb.append(",main_user_id='").append(user.getUserId()).append("'");
		sb.append(",main_user_phone='").append(user.getPhone()).append("'");
		sb.append(",son_user_name='").append(audit.getSonUserName()).append("'");
		sb.append(",son_user_phone='").append(audit.getSonUserPhone()).append("'");
		sb.append(",identityCard='").append(audit.getIdentityCard()).append("'");
		sb.append(",identityCardAddress='").append(audit.getIdentityCardAddress()).append("'");
		sb.append(",period='").append(audit.getPeriod()).append("'");
		sb.append(",identify_photo_front='").append(audit.getIdentifyPhotoFront()).append("'");
		sb.append(",identify_photo_reverse='").append(audit.getIdentifyPhotoReverse()).append("'");
		sb.append(",identify_photo_hand='").append(audit.getIdentifyPhotoHand()).append("'");
        sb.append(",identify_photo_head='").append(audit.getIdentifyPhotoHead()).append("'");
		sb.append(",sign_picture='").append(audit.getSignPicture()).append("'");
		sb.append(",order_status=").append(audit.getOrderStatus());
		sb.append(",relation_type=").append(audit.getRelationType());
		sb.append(",city_name='").append(audit.getCityName()).append("'");
		sb.append(",city_code='").append(audit.getCityCode()).append("'");
		sb.append(",user_type_id=").append(user.getUserTypeId());
		sb.append(",merchant_id='").append(user.getMerchantId()).append("'");
		sb.append(",imgSource=").append(audit.getImgSource());
		sql_list.add(sb.toString());

		sb.delete(0, sb.length());
		sb.append("insert into tb_job_number_audit_log set ");
		sb.append("order_id='").append(audit.getOrderId()).append("'");
		sb.append(",create_time=").append(audit.getCreateTime());
		sb.append(",status=").append(1);
		sql_list.add(sb.toString());

		int result[] = executeBatch(sql_list);
		return result != null && result.length > 0;

	}

	@Override
	public boolean addLimitPower(List<TbMerchantPowerLimit> limitList) {
		List<String> sqlList = new ArrayList<>();
		StringBuilder sb = new StringBuilder();
		for (TbMerchantPowerLimit limit : limitList) {
			sb.delete(0, sb.length());
			sb.append("insert into tb_merchant_power_limit set ");
			sb.append("dealer_id='").append(limit.getDealerId()).append("'");
			sb.append(",power_type=").append(limit.getPowerType());
			sb.append(",create_time=").append(limit.getCreateTime());
			sb.append(",desc_str='").append(limit.getDescStr()).append("'");
			sqlList.add(sb.toString());
		}
		int[] r = executeBatch(sqlList);
		return r != null && r.length > 0;
	}

	@Override
	public TbMerchantPowerLimit searchPowerLimit(String dealerId, MerchantPowerLimit limit) {
		try (ServerSqlSession session = getSqlDataSource().session()) {
			StringBuilder sb = new StringBuilder(256);
			sb.append(
					"SELECT dealer_id,power_type from tb_merchant_power_limit where dealer_id=? and power_type=? limit 1");
			return session.executeQuery(sb.toString(), rs -> {
				TbMerchantPowerLimit powerLimit = null;
				if (rs != null && rs.next()) {
					powerLimit = new TbMerchantPowerLimit();
					powerLimit.setDealerId(rs.getString("dealer_id"));
					powerLimit.setPowerType(rs.getInt("power_type"));
				}
				return powerLimit;
			}, dealerId, limit.getPower());
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

    @Override
    public List<RelationModel> getRelationTypeList() {
        try (ServerSqlSession session = getSqlDataSource().session()) {
            StringBuilder sb = new StringBuilder(256);
            sb.append("select relation_key,relation_name ");
            sb.append(" from tb_relation_type ");
            sb.append(" order by create_time desc ");
            return session.executeQuery(sb.toString(), rs -> {
                List<RelationModel> list = new ArrayList<>();
                if (rs != null) {
                    while (rs.next()) {
                        RelationModel model = new RelationModel();
                        model.setRelationKey(rs.getString("relation_key"));
                        model.setRelationName(rs.getString("relation_name"));
                        list.add(model);
                    }
                }
                return list;
            });
        } catch (Exception ex) {
            log.error(ex.getMessage(), ex);
            return null;
        }
    }

    @Override
    public SonJobNumProtocolModel getSonJobNumProtocol(String userId) {
        try (ServerSqlSession session = getSqlDataSource().session()) {
            StringBuilder sql = new StringBuilder(256);
            sql.append("SELECT c.user_name,c.is_main,c.userid,a.sign_picture,a.main_user_name");
            sql.append(" FROM tb_user_client c");
            sql.append(" LEFT JOIN tb_job_number_audit a on c.userid = a.user_id and a.order_status = 1 ");
            sql.append(" WHERE c.userid=?");
            sql.append(" limit 1");
            return session.executeQuery(sql.toString(), rs -> {
                if (rs != null && rs.next()) {
                    SonJobNumProtocolModel model = new SonJobNumProtocolModel();
                    model.setUserId(rs.getString("c.userid"));
                    model.setIsMain(rs.getString("c.is_main"));
                    model.setSignPicture(AliOSSUtil.generatePresignedUrl(AliOSSUtil.getDownloadUrl(rs.getString("a.sign_picture"))));
                    return model;
                }
                log.warn("DB not found user,userId=" + userId);
                return null;
            }, userId);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return null;
        }
    }

    @Override
    public Merchant getMerchantById(String merchantId) {
        try (ServerSqlSession session = getSqlDataSource().session()) {
            StringBuilder sb = new StringBuilder(256);
            sb.append("select dealer_id,company_name,user_type_id");
            sb.append(",business_licence,address,popchannel,head_popchannel");
            sb.append(",createtime,devMac,merchant_type,isSignAgreement");
            sb.append(",sign_time,sign_picture,bond,status");
            sb.append(",merchant_attribute,attribute_status,flag,banner_ids");
            sb.append(",super_dealer_id,super_dealer_name,super_invite_code");
            sb.append(",ytsk_status,detail_type");
            sb.append(" from tb_merchant");
            sb.append(" where merchant_id=?");
            sb.append(" limit 1");

            return session.executeQuery(sb.toString(), rs -> {
                if (rs != null && rs.next()) {
                    Merchant merchant = new Merchant();
                    merchant.setCompanyName(rs.getString("company_name"));
                    merchant.setUserTypeId(rs.getInt("user_type_id"));
                    merchant.setBusinessLicence(rs.getString("business_licence"));
                    merchant.setAddress(rs.getString("address"));
                    merchant.setPopChannel(rs.getString("popchannel"));
                    merchant.setHeadPopChannel(rs.getString("head_popchannel"));
                    merchant.setCreateTime(rs.getLong("createtime"));
                    merchant.setDevMac(rs.getString("devMac"));
                    merchant.setMerchantType(rs.getInt("merchant_type"));
                    merchant.setIsSignAgreement(rs.getInt("isSignAgreement"));
                    merchant.setSignTime(rs.getLong("sign_time"));
                    merchant.setSignPicture(rs.getString("sign_picture"));
                    merchant.setBond(rs.getInt("bond"));
                    merchant.setStatus(rs.getInt("status"));
                    merchant.setMerchant_attribute(rs.getInt("merchant_attribute"));
                    merchant.setAttribute_status(rs.getInt("attribute_status"));
                    merchant.setFlag(rs.getInt("flag"));
                    merchant.setBannerIds(rs.getString("banner_ids"));
                    merchant.setSuperDealerId(rs.getString("super_dealer_id"));
                    merchant.setSuperDealerName(rs.getString("super_dealer_name"));
                    merchant.setYtskStatus(rs.getInt("ytsk_status"));
                    merchant.setDetailType(rs.getInt("detail_type"));
                    merchant.setSuperInviteCode(rs.getString("super_invite_code"));
                    return merchant;
                }
                return null;
            }, merchantId);
        } catch (Exception ex) {
            log.error(ex.getMessage(), ex);
            return null;
        }
    }

    @Override
    public SdkAppConfModel getAppIdByDetailType(Integer detailType) {
        try (ServerSqlSession session = getSqlDataSource().session()) {
            StringBuilder sql = new StringBuilder(256);
            sql.append("SELECT app_id,living_check_flag ");
            sql.append(" FROM tb_sdk_app_conf");
            sql.append(" WHERE detail_type=?");
            sql.append(" limit 1");
            return session.executeQuery(sql.toString(), rs -> {
                if (rs != null && rs.next()) {
                    SdkAppConfModel model = new SdkAppConfModel();
                    model.setAppId(rs.getString("app_id"));
                    model.setLivingCheckFlag(rs.getInt("living_check_flag"));
                   return model;
                }
                log.warn("DB not found appid,detailType=" + detailType);
                return null;
            }, detailType);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return null;
        }
    }
}